How to manage timezone ?  #645

Activity

1. pashagolub commented on Mar 19, 2024

   

   pashagolub

   on Mar 19, 2024

   Last edited by pashagolub

   Collaborator

   More actions

   All tasks executed at server time
   https://pg-timetable.readthedocs.io/en/master/components.html#chain
2. gaslitbytech commented on Mar 24, 2024

   

   gaslitbytech

   on Mar 24, 2024

   Last edited by gaslitbytech

   More actions

   Though I'm not a go programmer I've taken some time to stare at the code a little while I think it might be feasible to change

   	const sqlSelectChains = sqlSelectLiveChains + ` AND NOT COALESCE(starts_with(run_at, '@'), FALSE) AND timetable.is_cron_in_time(run_at, now())`

   To

   	const sqlSelectChains = sqlSelectLiveChains + ` AND NOT COALESCE(starts_with(run_at, '@'), FALSE) AND timetable.is_cron_in_time(run_at, now() AT TIME ZONE XXXX)`

   Where XXXX is either an cmd line arg (or environmental variable) essentially like CRON_TZ would behave. Or alternatively n field in chain run_at_time_zone that allows for different time zones per chain. Perhaps both approaches make sense where if there is a per chain config use it otherwise use the cmd line / env variable if set and if only both null default to the system time zone as it currently is.

   A consequence of this will change the timestamp with time zone param of is_cron_in_time to timestamp without time zone though the way this function compares each time component perhaps this the right way to go.

   I'm willing to work on this with a little direction if you think this is the right approach.

   Edit: added idea to use both per chain and cmd line config.
3. bpasserat commented on Mar 24, 2024

   

   bpasserat

   on Mar 24, 2024

   Author

   More actions

   @gaslitbytech looks nice,
   My use case is with user defined cron task and user can be in differents timezones.
   So , I have a preference for per chain timezone with a default on system timezone.
4. gaslitbytech commented on Mar 24, 2024

   

   gaslitbytech

   on Mar 24, 2024

   Last edited by gaslitbytech

   More actions

   Nice. I've given it a go with this Draft PR #646

   Based on the above I opted for just adding a time zone per chain. My attempts of also adding a config using cmd line argument / environment variable got a little messy so I reverted that.
5. pashagolub commented on Mar 25, 2024

   

   pashagolub

   on Mar 25, 2024

   Collaborator

   More actions

   If one wants to run pg_timetable in a different time zone just use this:

   pg_timetable --clientname=bill postgresql://scheduler:somestrong@localhost:5432/timetable?timezone=UTC
   -- or
   pg_timetable --clientname=bill postgresql://scheduler:somestrong@localhost:5432/timetable?timezone=Etc/GMT+3
   -- or
   pg_timetable --clientname=bill postgresql://scheduler:somestrong@localhost:5432/timetable?timezone=Asia/Ho_Chi_Minh

   One can check pg_timezone_abbrevs or pg_timezone_names for time zones
6. cybertec-postgresql

   locked and limited conversation to collaborators on Mar 25, 2024
7. pashagolub

   converted this issue into a discussion #647 on Mar 25, 2024