SQL grouping result to half hour

I have a Firebird 2.5.9 database and I need to work on two tables. The first one stores a date and operation time. The date is stored as DATE field and 3 other fields as string (char) for year, month and day. The operation time is stored in 3 field as string (char) for hour, minute and second. The second table stores the amount in Euro as float.

With this query, I can get the amounts hour by hour:

SELECT Table1.Day AS TheDay, Table1.HH AS TH, SUM(Table2.Euro) AS EURO
FROM Table1       
INNER JOIN Table2 ON Table2.NUMOP = Table1.NUMOP 
WHERE Table1.DAY = '2024-08-31'
GROUP BY TABLE1.DAY, TABLE1.HH

The result is the total Euros divided by hours (8, 9, 10, 11, 12), but the working hours are from 8.30 to 12.30 and the result I need to get must be 8.30, 9.00, 9.30, 10.00 .. 12.30 and 13.00 (if one or more sell happened after 12.30).

I found several answers on how get this result, but all of them work with timestamp fields, a field that is missing in both tables. I tried to change some query without any result.

These are the posts I read:

I tried to "create" the timestamp in a first select and in a second select extract the required data, but I'm not able to do the second query. The first query is:

select CAST(Table1.ANNO || '-' || Table1.MESE || '-' || Table1.GIORNO || ' ' 
   || Table1.HH || ':' || Table1.MM AS timeStamp) AS DT1 
FROM Table1
WHERE Table1.Giorno = '2024-08-31'

The result is a timestamp field, but I don't know how to use it for the second select.

How can I get the correct result?

NOTE: I can't change the database because it is from a third party application. I must only read the data.

After readed answers, suggestions and testing

Thank you for all your help. I tryed to do some test ... and all of them without result. In the first query i add the minute:

SELECT Table1.Day AS TheDay, Table1.HH AS TH,
       Table1.MM AS TM, SUM(Table2.Euro) AS EURO
FROM Table1       
INNER JOIN Table2 ON Table2.NUMOP = Table1.NUMOP 
WHERE Table1.DAY = '2024-08-31'
GROUP BY TABLE1.DAY, TABLE1.HH, Table1.MM

and the result is

After i ad to the query the user13964273 suggestion:

SELECT Table1.Day AS TheDay, Table1.HH AS TH,
       Table1.MM AS TM, SUM(Table2.Euro) AS EURO
FROM Table1       
INNER JOIN Table2 ON Table2.NUMOP = Table1.NUMOP 
WHERE Table1.DAY = '2024-08-31'
GROUP BY Table1.HH * 2 + Table1.MM / 30

without casting because in the test table hours and minute are defines as integer. I have this in DBeaver

So i correct the query with adding TABLE1.HH and Table1.MM in group by:

SELECT Table1.Day AS TheDay, Table1.HH AS TH,
       Table1.MM AS TM, SUM(Table2.Euro) AS EURO
FROM Table1       
INNER JOIN Table2 ON Table2.NUMOP = Table1.NUMOP 
WHERE Table1.DAY = '2024-08-31'
GROUP BY TABLE1.DAY, TABLE1.HH, Table1.MM,
         Table1.HH * 2 + Table1.MM / 30

With this result:

I'm unable to aggregate result by half hour even with first clas help from you !

How i have to write the query ?

Solved

Thanks to all, i learn a lot about time handle.

Problem solved with Simon answer.

Solution

Try this (this is just pseudo code too, you might need to make some small adjustments)

SELECT 
    CAST(Table1.Year || '-' || Table1.Month || '-' || Table1.Day AS DATE) AS 
TheDate, 
    CAST(Table1.HH || ':' || Table1.MM || ':00' AS TIME) AS TheTime,
    SUM(Table2.Euro) AS TotalEuro
FROM 
    Table1
INNER JOIN 
    Table2 ON Table2.NUMOP = Table1.NUMOP 
WHERE 
    Table1.Year = '2024' AND Table1.Month = '08' AND Table1.Day = '31'
GROUP BY 
    TheDate, 
    EXTRACT(HOUR FROM TheTime) + CASE WHEN EXTRACT(MINUTE FROM TheTime) >= 30 
THEN 0.5 ELSE 0 END

Date and Time Construction: The date and time are constructed from separate string fields into a timestamp format. Here, only the hour and minute are used to construct a time, and seconds are assumed to be zero.

Grouping by Half-Hour Intervals: The GROUP BY clause groups records into half-hour intervals. This is done by extracting the hour from the time and adjusting it by adding 0.5 if the minutes are 30 or greater.