Search code examples
sqldatabasepostgresqldate-arithmetic

Extract hours as 1 - 48 from half hour interval times

I have the below data.

0:00:00
0:30:00
1:00:00
1:30:00
2:00:00
2:30:00
3:00:00
3:30:00
4:00:00
4:30:00
5:00:00
5:30:00
6:00:00
6:30:00

I can extract the hour the using EXTRACT(HOUR FROM TIMESTAMP) but this will give me 24 hours.

But now I need to some different calculation where I can get numbers from 1-48 based on the time given.

Something like this:

0:00:00 1
0:30:00 2
1:00:00 3
1:30:00 4
2:00:00 5
2:30:00 6
3:00:00 7
3:30:00 8
4:00:00 9
4:30:00 10
6:00:00 13
6:30:00 14

Note the skipped 11 and 12, for the absent values 5:00 and 5:30.

Is there any possibilities that I can get that result in PostgreSQL?

Solution

  • Simply use formula 1 + extract(hour from 2 * tm) - it gives your expected result exactly - obligatory SQLFiddle.