Is there a function to extract the time part of a datetime variable in Trino SQL, including nanoseconds? --AND/OR-- Is there a corresponding comparison operator that allows comparison to just the time part of a datetime variable in Trino SQL (including nanoseconds)?
I know I can use HOUR(), MINUTE(), SECOND() to extract these elements, but I can't find a "one-shot" function to extract the time from a timestamp.
For example, if the time is '2020-05-11 11:15:05.123456789 +01:00', I would like to know if there is a function that returns '11:15:05.123456789' (i.e., no date part). I can cast the datetime to time (e.g., CAST(my_date_time_variable as time), but this doesn't give fractional seconds. E.g., CAST('2020-05-11 11:15:05.123456789 +01:00' as time) yields '11:15:05', dropping the fractional seconds.
magical_trino_sql_function('2020-05-11 11:15:05.123456789 +01:00')
-- '11:15:05.123456789'
or
magical_trino_sql_function('2020-05-11 11:15:05.123456789 +01:00')
-- 11:15:05.123456789
Why do I want to do this?
I would like to use a WHERE statement like the following.
WHERE magical_trino_sql_function(date_time_variable) BETWEEN 09:30:00.000000000 and 16:00:00.000000000
This is the clunky code that comes close to working now:
WHERE my_date_time_variable BETWEEN date_add('minute', 30, date_add('hour', 9, my_date_variable)) AND
date_add('hour', 16, my_date_variable)
where my_date_time_variable looks like 2020-05-11 11:15:05.123456789 +01:00 and my_date_variable looks like 2020-05-11.
A problem I encounter is that the fractional seconds past milliseconds seem to be truncated and the resulting time is rounded, so something like 2020-05-11 09:29:59.999876543 +01:00 gets rounded to 2020-05-11 09:30:00.000 +01:00, but I wouldn't want to include that in the above WHERE statement.
When I compare dates, I use something like
WHERE date_variable BETWEEN date'2023-07-01' AND date'2023-07-31'
Is there something to compare just the time part of a datetime variable with fractional seconds? For example:
WHERE my_date_time_variable BETWEEN time'09:30:00.000' AND time'16:00:00.000'
The nanoseconds matter, but I'm having problems with this as well; see this question.
I have searched the Trino documentation and the typical search engines and have come up empty. I also reviewed the questions that come up when asking the question to make sure my question isn't a duplicate. Some of the questions helped me improve my question, but I'm still having problems.
First, you should circumvent the Trino limitation on nanoseconds by treating the value as a string rather than a timestamp. The second step involves separately extracting the time without nanoseconds and the nanosecond component itself. Finally, calculate the total number of nanoseconds so you can use it in the WHERE clause with BETWEEN:
with temp_table as
(SELECT cast(split(split( '2023-01-10 09:30:00.123456789', ' ')[2],'.')[1] as time) as houres_minutes_seconds,
cast(split(split( '2023-01-10 09:30:00.123456789', ' ')[2],'.')[2] as int) as nanoseconds)
SELECT
(EXTRACT(hour FROM houres_minutes_seconds) * 3600000000000) +
(EXTRACT(minute FROM houres_minutes_seconds) * 60000000000) +
(EXTRACT(second FROM houres_minutes_seconds) * 1000000000) + nanoseconds AS total_nanoseconds
from temp_table