Postgres time comparaison with time zone-CodePudding

Today I encounter a strange postgres behavious. Let me explain:

Here is my table I will work on.

=># \d planning_time_slot 
                                        Table "public.planning_time_slot"
   Column    |           Type            | Collation | Nullable |                    Default                     
------------- --------------------------- ----------- ---------- ------------------------------------------------
 id          | integer                   |           | not null | nextval('planning_time_slot_id_seq'::regclass)
 planning_id | integer                   |           | not null | 
 day         | character varying(255)    |           | not null | 
 start_time  | time(0) without time zone |           | not null | 
 end_time    | time(0) without time zone |           | not null | 
 day_id      | integer                   |           | not null | 0
Indexes:
    "planning_time_slot_pkey" PRIMARY KEY, btree (id)
    "idx_a9e3f3493d865311" btree (planning_id)
Foreign-key constraints:
    "fk_a9e3f3493d865311" FOREIGN KEY (planning_id) REFERENCES planning(id)

what i want to do is something like:

select * from planning_time_slot where start_time > (CURRENT_TIME AT TIME ZONE 'Europe/Paris');

But it seems like postgres is comparing time before the time zone conversion. Here is my tests:

=># select * from planning_time_slot where start_time > (CURRENT_TIME AT TIME ZONE 'Europe/Paris');
 id  | planning_id | day | start_time | end_time | day_id 
----- ------------- ----- ------------ ---------- --------
 157 |           6 | su  | 16:00:00   | 16:30:00 |      0
(1 row)
=># select (CURRENT_TIME AT TIME ZONE 'Europe/Paris');
      timezone      
--------------------
 16:35:48.591002 02
(1 row)

When I try with a lot of entries it appears that the comparaison is done between start_time and CURRENT_TIME without the time zone cast.

For your information I also tried :

select * from planning_time_slot where start_time > timezone('Europe/Paris', CURRENT_TIME);

It has the exact same result.

I also tried to change the column type to time(0) with time zone. It makes the exact same result.

One last important point. I really need to set timezone I want, because later on I will change it dynamically depending on other stuffs. So it will not be 'Europe/Paris' everytime.

Does anyone have a clue or a hint please ?

psql (PostgreSQL) 11.2 (Debian 11.2-1.pgdg90 1)

CodePudding user response：

I think you have deeper problems.

You have a day, a start time and an end time, but no notion of time zone. So this will mean something different depending on the time zone of the observer.

I think you should add a tz column that stores which time zone that information is in. Then you can get the start time like this:

WHERE (day   start_time) AT TIME ZONE tz > current_timestamp

CodePudding user response：

(CURRENT_TIME AT TIME ZONE 'Europe/Paris') is, for example, 17:52:17.872082 02. But internally it is 15:52:17.872082 00. Both time and timetz (time with time zone) are all stored as UTC, the only difference is timetz is stored with a time zone. Changing the time zone does not change what point in time it represents.

So when you compare it with a time...

# select '17:00:00'::time < '17:52:17 02'::timetz;
 ?column? 
----------
 f

That is really...

# select '17:00:00'::time < '15:52:17'::time;
 ?column? 
----------
 f

Casting a timetz to a time will lop off the time zone.

test=# select (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
    timezone     
-----------------
 17:55:57.099863
(1 row)

test=# select '17:00:00' < (CURRENT_TIME AT TIME ZONE 'Europe/Paris')::time;
 ?column? 
----------
 t

Note that this sort of comparison only makes sense if you want to store the notion that a thing happens at 17:00 according to the clock on the wall. For example, if you had a mobile phone game where an event starts "at 17:00" meaning 17:00 where the user is. This is referred to as a "floating time zone".

Assuming day is "day of week", I suggest storing it as an integer. It's easier to compare and localize.
Instead of separate start and end times, consider a single timerange. Then you can use range operators.