timestamp fields in oban_jobs table doesn't have time zone causing AT TIME ZONE shift incorrect time

[wingyplus]

Environment

Oban Version

2.14.2

PostgreSQL Version

select version();
PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

Elixir & Erlang/OTP Versions (elixir --version)

Erlang/OTP 25 [erts-13.1.3] [source] [64-bit] [smp:10:10] [ds:10:10:10] [async-threads:1] [jit] [dtrace]

Elixir 1.14.3 (compiled with Erlang/OTP 25)

Current Behavior

I found when try to query inserted_at with my local time zone (Asia/Bangkok) which's +0700 hours on the oban_jobs table to investigate our production issue by:

SELECT * FROM oban_jobs
WHERE DATE(inserted_at AT TIME ZONE 'Asia/Bangkok') == '2023-03-16' and worker = 'redacted'

The result returns date but it seems incorrect. So I start debugging by querying:

SELECT inserted_at, inserted_at AT TIME ZONE 'Asia/Bangkok' FROM oban_jobs;

Result show that inserted_at AT TIME ZONE 'Asia/Bangkok' -07:00 instead of +07:00 as per screenshot below:

Expected Behavior

Timestamp like inserted_at should +07:00 instead of -07:00.

[wingyplus]

I think it is probably because Oban using :utc_datetime_usec in migration which doesn't contain time zone information. Modify it to timestamptz would help, I guess. Another workaround is casting to timestamptz before do AT TIME ZONE:

[sorentwo]

This is on purpose, all of the timestamps are stored in UTC. You can change the column type for your own table if you like, but it may cause issues with some of Oban’s internal queries.