Get day and time as UTC of the current day and add its difference to a UTC timestamp

Question

I have a query like this:

SELECT *
FROM mytable fact
JOIN time_table time ON ( time.time_5_min_utc = fact.event_5_min_utc )
WHERE fact.event_utc >= ${fromTimestamp}
  AND fact.event_utc < ${toTimestamp} 
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc < ${toTimestamp}

I always know the fromTimestamp in UTC.

What I want to to now is add value to toTimestamp to get the end of the day.

Something like:

SELECT *
FROM mytable fact
JOIN time_table time ON ( time.time_5_min_utc = fact.event_5_min_utc )
WHERE fact.event_utc >= ${fromTimestamp}
  AND fact.event_utc < ${toTimestamp} + 86400000
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc < ${toTimestamp} + 86400000

This should add 86400000 to the toTimestamp but what I get is a +24h. It should be the end of current day 23:59:59.

How can I do that?

Answer 1

I found a way how to fix this problem and I wanted to share my solution.

CAST (
       to_utc_timestamp (
                 date_add (
                     date_add (
                         date_trunc (
                             'day',
                             CAST (
                                   CAST (
                                       ${toTimestamp} AS DECIMAL (30, 0))
                                 / 1000
                                     AS TIMESTAMP)),
                         1),
                       interval
                     - 1 seconds),
                 'myNeededTimezone')
                 AS BIGINT)
       * 1000

Explanation:
First the 'toTimestamp is cast as decimal to be able to cast it as timestamp afterwards. When it is a timestamp it is possible to extract the day of it and then subtract 1 second to get the day before 23:59:59. Then it is converted back to a UTC timestamp with the correct timezone. At last it is converted back as number and multiplied with 1000 to get a UTC timestamp in milliseconds.