Postgres time 00 and 24 hours

Question

Why the following query return false?

SELECT ('00:00:00'::TIME) = ('24:00:00'::TIME) AS "time", ('00:00:00'::TIMETZ) = ('24:00:00'::TIMETZ) AS "timetz"

Result:

+-------+--------+
| time  | timetz |
+-------+--------+
| false | false  |
+-------+--------+

While the result of the following query is same!!

SELECT ('00:00:00'::TIME) AS "time1", ('24:00:00'::TIME) AS "time2", ('00:00:00'::TIMETZ) AS "timetz1" , ('24:00:00'::TIMETZ) AS "timetz2"

Result:

+----------+----------+------------------------+------------------------+
|  time1   |  time2   |        timetz1         |        timetz2         |
+----------+----------+------------------------+------------------------+
| 00:00:00 | 00:00:00 | 00:00:00.000000 +00:00 | 00:00:00.000000 +00:00 |
+----------+----------+------------------------+------------------------+

What is difference between 00:00:00 and 24:00:00 in postgres?

Answer 1

Postgres' time type is defined to have a range of '00:00:00' to '24:00:00', inclusive on both ends. To see why the two endpoints are not the same, consider:

SELECT '00:00:00'::time AS start, '24:00:00'::time AS end;

For the end value, I see 1.00:00:00, contrary to what you see, indicating that 24 hours is actually one day, with zero hours, minutes, and seconds.