I am trying to do a time series and convert what is 'utc' time into another zone.
I have this code in Elixir:
sd = ~U[2024-07-31 05:00:00.000000Z]
ed = ~U[2024-08-02 04:59:59.000999Z]
timezone = "US/Central"
from(
c in fragment(
"SELECT generate_series(now() AT TIME ZONE 'UTC' AT TIME ZONE ?, (now() AT TIME ZONE 'UTC' AT TIME ZONE ?) - CAST(? AS INTERVAL), '-1 hour') AS entry",
^timezone,
^timezone,
^%Postgrex.Interval{secs: DateTime.diff(ed, sd)}
),
select: %{
date_str:
fragment(
"to_char(? AT TIME ZONE ?,'YYYY-MM-DD HH24:MI:SS TZ')",
c.entry,
^timezone
)
}
)
I get these results in utc instead of the zone I expect: I want 'US/central' not "" or 'UTC'
data: [
%{date_str: "2024-08-01 16:21:21 "},
%{date_str: "2024-08-01 15:21:21 "},
%{date_str: "2024-08-01 14:21:21 "},
%{date_str: "2024-08-01 13:21:21 "},
%{date_str: "2024-08-01 12:21:21 "},
%{date_str: "2024-08-01 11:21:21 "},
%{date_str: "2024-08-01 10:21:21 "},
%{date_str: "2024-08-01 09:21:21 "},
%{date_str: "2024-08-01 08:21:21 "},
%{date_str: "2024-08-01 07:21:21 "},
%{date_str: "2024-08-01 06:21:21 "},
%{date_str: "2024-08-01 05:21:21 "},
%{date_str: "2024-08-01 04:21:21 "},
%{date_str: "2024-08-01 03:21:21 "},
%{date_str: "2024-08-01 02:21:21 "},
%{date_str: "2024-08-01 01:21:21 "},
%{date_str: "2024-08-01 00:21:21 "},
%{date_str: "2024-07-31 23:21:21 "},
%{date_str: "2024-07-31 22:21:21 "},
%{date_str: "2024-07-31 21:21:21 "},
%{date_str: "2024-07-31 20:21:21 "},
%{date_str: "2024-07-31 19:21:21 "},
%{date_str: "2024-07-31 18:21:21 "},
%{date_str: "2024-07-31 17:21:21 "},
%{date_str: "2024-07-31 16:21:21 "},
%{date_str: "2024-07-31 15:21:21 "},
%{date_str: "2024-07-31 14:21:21 "},
%{date_str: "2024-07-31 13:21:21 "},
%{date_str: "2024-07-31 12:21:21 "},
%{date_str: "2024-07-31 11:21:21 "},
%{date_str: "2024-07-31 10:21:21 "},
%{date_str: "2024-07-31 09:21:21 "},
%{date_str: "2024-07-31 08:21:21 "},
%{date_str: "2024-07-31 07:21:21 "},
%{date_str: "2024-07-31 06:21:21 "},
%{date_str: "2024-07-31 05:21:21 "},
%{date_str: "2024-07-31 04:21:21 "},
%{date_str: "2024-07-31 03:21:21 "},
%{date_str: "2024-07-31 02:21:21 "},
%{date_str: "2024-07-31 01:21:21 "},
%{date_str: "2024-07-31 00:21:21 "},
%{date_str: "2024-07-30 23:21:21 "},
%{date_str: "2024-07-30 22:21:21 "},
%{date_str: "2024-07-30 21:21:21 "},
%{date_str: "2024-07-30 20:21:21 "},
%{date_str: "2024-07-30 19:21:21 "},
%{date_str: "2024-07-30 18:21:21 "},
%{date_str: "2024-07-30 17:21:21 "}
]
This code change results in 'utc'
from(
c in fragment(
"SELECT generate_series(now() AT TIME ZONE ?, (now() AT TIME ZONE ?) - CAST(? AS INTERVAL), '-1 hour') AS entry",
^timezone,
^timezone,
^%Postgrex.Interval{secs: DateTime.diff(range.end_date, range.start_date)}
),
select: %{
date_str:
fragment(
"to_char(? AT TIME ZONE ?,'YYYY-MM-DD HH24:MI:SS TZ')",
) )
new results:
%{date_str: "2024-08-01 17:24:22 UTC"},
%{date_str: "2024-08-01 16:24:22 UTC"},
%{date_str: "2024-08-01 15:24:22 UTC"},
%{date_str: "2024-08-01 14:24:22 UTC"},
%{date_str: "2024-08-01 13:24:22 UTC"},
%{date_str: "2024-08-01 12:24:22 UTC"},
%{date_str: "2024-08-01 11:24:22 UTC"},
%{date_str: "2024-08-01 10:24:22 UTC"},
%{date_str: "2024-08-01 09:24:22 UTC"},
%{date_str: "2024-08-01 08:24:22 UTC"},
%{date_str: "2024-08-01 07:24:22 UTC"},
%{date_str: "2024-08-01 06:24:22 UTC"},
%{date_str: "2024-08-01 05:24:22 UTC"},
%{date_str: "2024-08-01 04:24:22 UTC"},
%{date_str: "2024-08-01 03:24:22 UTC"},
%{date_str: "2024-08-01 02:24:22 UTC"},
%{date_str: "2024-08-01 01:24:22 UTC"},
%{date_str: "2024-08-01 00:24:22 UTC"},
turns out tz in to_char() is not well supported in postgres for the long version of a zone like 'US/Central' or others. The OF [for offset] works fine.
this worked for me:
from(
c in fragment(
"SELECT generate_series(now() AT TIME ZONE ?, (now() AT TIME ZONE ?) - CAST(? AS INTERVAL), '-1 hour') AS entry",
^timezone,
^timezone,
^%Postgrex.Interval{secs: DateTime.diff(range.end_date, range.start_date)}
),
select: %{
date_str:
fragment(
"to_char(? AT TIME ZONE ?,'YYYY-MM-DD HH12:MI:SS')",
c.entry,
^timezone
)
}
)
)