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

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.

CodePudding user response：

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)

CodePudding user response：

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      |
 ------------