How to manage timezone ?

[bpasserat]

Is your feature request related to a problem? Please describe.
I use pg_timetable to send email periodically according to a cron task created by a user.
The cron task is given in the user localtime, but it is exexcuted by pg_timetable as UTC task so I have a gap between the user defined time and the execution time.

I would like to know if pg_timetable handle this case and how. I did not find any reference to timezone into the docs.

[pashagolub]

All tasks executed at server time
https://pg-timetable.readthedocs.io/en/master/components.html#chain

[gaslitbytech]

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.

[bpasserat]

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

[gaslitbytech]

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.

[pashagolub]

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