Comparing Two Times

[Deleted User] · July 2018

Jim Knicely authored this tip.

There are several cool built-in functions that you can use to compare two times.

Example:

dbadmin=> SELECT time_now AS time1,
dbadmin->        time_now_minus_1_hour AS time2,
dbadmin->        TIME_EQ(time_now, time_now_minus_1_hour) AS "time1 = time2",
dbadmin->        TIME_GE(time_now, time_now_minus_1_hour) AS "time1 >= time2",
dbadmin->        TIME_GT(time_now, time_now_minus_1_hour) AS "time1 > time2",
dbadmin->        TIME_LE(time_now, time_now_minus_1_hour) AS "time1 <= time2",
dbadmin->        TIME_LT(time_now, time_now_minus_1_hour) AS "time1 < time2"
dbadmin->  FROM (SELECT sysdate::TIME AS time_now,
dbadmin(>               (sysdate - INTERVAL '1 HOUR')::TIME AS time_now_minus_1_hour) foo;
      time1      |      time2      | time1 = time2 | time1 >= time2 | time1 > time2 | time1 <= time2 | time1 < time2
-----------------+-----------------+---------------+----------------+---------------+----------------+---------------
20:04:38.369828 | 19:04:38.369828 | f             | t              | t             | f              | f
(1 row)

Have fun!