How to compare the timestamp of the exact hours/minute/seconds?

user_3461502 0
Viewed 227

I have to columns:

BEFORE_TIME & AFTER_TIME
2021-09-02 09:41:00.000 || 2021-09-09 09:41:00.000
2021-09-03 09:09:07.000 || 2021-09-10 09:09:07.000
2021-09-03 13:45:48.000 || 2021-09-10 15:45:48.000

I want to compare the hours/minutes and seconds to see if they match. I can ignore the years/months and days. Is there a way I can do this? For example, in the above, I know the first two records match since the have the same hours, minutes and seconds in both columns, but the last record does not match.

snowflake-cloud-data-platform sql
2 Answers

You can use trunc() to truncate the timestamp to seconds, and cast to ::time to remove the date part:

select a=b
    , trunc(a::time, 'second') = trunc(b::time, 'second')
from (
    select '2021-01-01 10:10:10.313'::timestamp a
        ,  '2021-08-01 10:10:10.444'::timestamp b
)

(Which gives false and true)

user_1324380

Snowflake provides HOUR(), MINUTE() and SECOND() functions:

https://docs.snowflake.com/en/sql-reference/functions/hour-minute-second.html

WITH t AS (
  SELECT 
    '2021-09-02 09:41:00.000'::timestamp as before_time,
    '2021-09-09 09:41:00.000'::timestamp as after_time
  UNION
  SELECT 
    '2021-09-03 09:09:07.000'::timestamp as before_time,
    '2021-09-10 09:09:07.000'::timestamp as after_time
  UNION
  SELECT 
    '2021-09-03 13:45:48.000'::timestamp as before_time,
    '2021-09-10 15:45:48.000'::timestamp as after_time
)
SELECT 
  hour(before_time) = hour(after_time)
  and minute(before_time) = minute(after_time)
  and second(before_time) = second(before_time) as time_check
FROM
t;

Or you can put it inside a UDF:

CREATE OR REPLACE FUNCTION time_check (
  BEFORE_TIME timestamp,
  AFTER_TIME timestamp
)
returns boolean
LANGUAGE JAVASCRIPT
as 
$$
  // do some validation here

  var before = new Date(BEFORE_TIME);
  var after = new Date(AFTER_TIME);
  
  
  return before.getSeconds() == after.getSeconds() &&
    before.getMinutes() == after.getMinutes() &&
    before.getHours() == after.getHours();
$$
;

WITH t AS (
  SELECT 
    '2021-09-02 09:41:00.000'::timestamp as before_time,
    '2021-09-09 09:41:00.000'::timestamp as after_time
  UNION
  SELECT 
    '2021-09-03 09:09:07.000'::timestamp as before_time,
    '2021-09-10 09:09:07.000'::timestamp as after_time
  UNION
  SELECT 
    '2021-09-03 13:45:48.000'::timestamp as before_time,
    '2021-09-10 15:45:48.000'::timestamp as after_time
)
SELECT 
  time_check(before_time, after_time) as time_check
FROM
t;

Both of above should return:

+------------+
| TIME_CHECK |
|------------|
| True       |
| True       |
| False      |
+------------+
user_98793790
Related