I'm building a Grafana dashboard where I need to apply a date filter.
I'm querying a Postgres database using SQL when I'm building the analysis and the date looks like the following:
| id | timestamp_column | start_time | end_time |
|---|---|---|---|
| 1 | 2023-04-16 16:00:00.000 | 1400 | 1900 |
| 2 | 2023-04-17 16:00:00.000 | 100 | 900 |
I would like the output to be the following in order for me to be able to use it as a filter:
| id | new_timestamp_start | new_timestamp_end |
|---|---|---|
| 1 | 2023-04-16 14:00:00.000 | 2023-04-16 19:00:00.000 |
| 2 | 2023-04-17 01:00:00.000 | 2023-04-17 09:00:00.000 |
The timestamp column is of type timestamp and the start_time and end_time columns are of type int and note that the time 0100 and 0900 therefore is stored as 100 and 900.
Super thankful for all help!
Regards
Here is one way to do it with date and interval arithmetics:
select id,
date_trunc('day', timestamp_column)
+ start_time / 100 * interval '1 hour'
+ start_time % 100 * interval '1' minute as new_timestamp_start,
date_trunc('day', timestamp_column)
+ end_time / 100 * interval '1 hour'
+ end_time % 100 * interval '1' minute as new_timestamp_end
from mytable
The idea is to break down the integer column into hours and minutes using division and modulo, then perform the date arithmetics.
Demo on DB Fiddle - I slightly modified the data so minutes come into play as well:
| id | new_timestamp_start | new_timestamp_end |
|---|---|---|
| 1 | 2023-04-16 14:00:00 | 2023-04-16 19:00:00 |
| 1 | 2023-04-16 01:00:00 | 2023-04-16 09:50:00 |