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

[gaslitbytech]

Including timezone as part of the connection string is neat and does bypass the need to want to pass a cmd line argument / environment variable I talked about earlier and honestly would suit my needs just fine.

The PR #646 was an draft attempt at satisfying the following preference by @bpasserat though.

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.

Thanks for running the unit tests with github. I'll get them going locally and work out what may be going wrong.

[gaslitbytech]

In regards to: #646 (comment)

This will never pass

The only unit test failure in https://github.com/cybertec-postgresql/pg_timetable/pull/646/checks is FAIL: TestMigrations (0.40s)

I worked out how to run the unit tests locally and worked out only the migration test was failing locally as well. The cause was with the way I was dropping the functions with deprecated function signature. I saw how previous migrations handled the drop using an if exists without specifying function signature and opted to follow this pattern in the latest commit in https://github.com/gaslitbytech/pg_timetable/pull/1/commits

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

Yes. Though I don't think this applies here as we are processing the timestamp at runtime and not storing it.

I also manual sanity tested the change as how it will affect the is_cron_in_time function with the below.

BEGIN;
-- Current behaviour
SET timezone='Australia/Melbourne';
SELECT date_part('hour', now());
SET timezone='Australia/Perth';
SELECT date_part('hour', now());

-- Optional per chain time zone override behaviour
SET timezone='UTC'; -- reset it.
SELECT date_part('hour', now() AT TIME ZONE 'Australia/Melbourne');
SELECT date_part('hour', now() AT TIME ZONE 'Australia/Perth');

-- Backwards compatible behaviour
SET timezone='Australia/Melbourne';
SELECT date_part('hour', now() AT TIME ZONE current_setting('timezone'));
SET timezone='Australia/Perth';
SELECT date_part('hour', now() AT TIME ZONE current_setting('timezone'));
ROLLBACK;

I believe this makes the per chain time zone override a neat feature.