issues with time cutoff applied to job query

[martenson]

Describe the bug
In my dev environment if I go to /admin/jobs and search for jobs using time cutoff all will have 120 minutes subtracted from their finish time i.e. if the job finished 2 minutes ago I will only see it if there is 122 entered in the cutoff. This is possibly caused during the timezone calculation since jobs use UTC?

I just checked and this seems to work fine on usegalaxy.org so possibly somehow related to my dev setup?

Galaxy Version and/or server at which you observed the bug
dev

Expected behavior
As a user I do not care what timezone the jobs are stored with, I want to see the cutoff applied to my current time I think.

[davelopez]

Possible regression of #14281 or #14315 although we have now tests covering it I think.

It seems to work on my local dev, the date_range_min is sent as UTC time:

http://127.0.0.1:8081/api/jobs?view=admin_job_list&date_range_min=2023-05-02T09:17:05.330Z

While my local time was 2023-05-02T11:22:05.330Z with a cutoff of 5 minutes.

Can you verify in the console that when you change the cutoff time the date_range_min param in the request is sent with the correct UTC - cutoff_minutes?

[mvdbeek]

I would also check what timezone is set in postgresql.conf (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE)

[martenson]

2 hours...

❯ cat /usr/local/var/postgres/postgresql.conf | grep zone
log_timezone = 'Europe/Prague'
timezone = 'Europe/Prague'

[mvdbeek]

I assume it works if you set it to GMT ? If yes I guess we should document this ?

[martenson]

yeah, if the DB is set to GMT (which is the default in the docs, but mine got definitely set up by an installer or sth) the cutoff works correctly

[mvdbeek]

https://devpress.csdn.net/python/63045c1a7e6682346619a7db.html suggests that we could also do something like engine = create_engine("...", connect_args={"options": "-c timezone=utc"}) if we know we're using psycopg2. How hard do you think it would be to do that @jdavcs ?

[jdavcs]

Hmm... That's an undocumented option (i.e., not a SQLAlchemy option, but a way to chain SA with psycopg2 with libpq with postgresql -c option that modifies postgres's config settings. So many moving parts; the moment one of these API changes, this pipeline breaks. Besides, as an admin I would be surprised if Galaxy would overwrite the timezone setting I have in my postgresql.conf. Not to mention we'd have to handle sqlite as well. My vote is to just update postgresql.conf.