Generate_series with zone from utc to another zone

Questions / Help
1

this is my query. my series always comes back in utc, need it in ‘US/Centeral’ for example

def date_series_sample(filters, acct) do
    range = extract_date_range(filters, acct)

    date_utils = DateUtils.new(acct)
    timezone = DateUtils.get_timezone(date_utils)

   dbg(range: range, tz: timezone)

    query =
      case range.expr do
        "hour" ->
          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')",
                  c.entry,
                  ^timezone
                )
            }
          )

        "day" ->
          from(
            c in fragment(
              "SELECT generate_series(now() AT TIME ZONE ?, (now() AT TIME ZONE ?) - CAST(? AS INTERVAL), '-1 day') AS entry",
              ^timezone,
              ^timezone,
              ^%Postgrex.Interval{days: DateTime.diff(range.end_date, range.start_date, :day)}
            ),
            select: %{
              date_str:
                fragment(
                  "cast(? AT TIME ZONE ? as date)",
                  c.entry,
                  ^timezone
                )
            }
          )

        "month" ->
          from(
            c in fragment(
              "SELECT generate_series(now() AT TIME ZONE ?, (now() AT TIME ZONE ?) - CAST(? AS INTERVAL), '-1 month') AS entry",
              ^timezone,
              ^timezone,
              ^%Postgrex.Interval{months: DateTime.diff(range.end_date, range.start_date, :day)}
            ),
            select: %{
              date_str:
                fragment(
                  "to_char(? AT TIME ZONE ?,'YYYY-MM')",
                  c.entry,
                  ^timezone
                )
            }
          )

        "quarter" ->
          from(
            c in fragment(
              "SELECT generate_series(date_trunc('quarter', ?::timestamp AT TIME ZONE ?), date_trunc('quarter', ?::timestamp AT TIME ZONE ?), '3 months') AS entry",
              ^range.start_date,
              ^timezone,
              ^range.end_date,
              ^timezone
            ),
            select: %{
              date_str:
                fragment(
                 "to_char(? AT TIME ZONE ?,'YYYY \"Q\"Q')",
                  c.entry,
                  ^timezone
                )
            }
          )

        "year" ->
          from(
            c in fragment(
              "SELECT generate_series(date_trunc('year', ?::timestamp AT TIME ZONE ?), date_trunc('year', ?::timestamp AT TIME ZONE ?), '12 months') AS entry",
              ^range.start_date,
              ^timezone,
              ^range.end_date,
              ^timezone
            ),
            select: %{
              date_str:
                fragment(
                  "to_char(? AT TIME ZONE ?,'YYYY')",
                  c.entry,
                  ^timezone
                )
            }
          )
      end

    Cogito.Repo.all(query)
  end

result:

[data: data] #=> [
  data: [
    %{date_str: "2024-08-01 04:49:09 UTC"},
    %{date_str: "2024-08-01 03:49:09 UTC"},
    %{date_str: "2024-08-01 02:49:09 UTC"},
    %{date_str: "2024-08-01 01:49:09 UTC"},
    %{date_str: "2024-08-01 00:49:09 UTC"},
    %{date_str: "2024-07-31 23:49:09 UTC"},
    %{date_str: "2024-07-31 22:49:09 UTC"},
    %{date_str: "2024-07-31 21:49:09 UTC"},
    %{date_str: "2024-07-31 20:49:09 UTC"},
    %{date_str: "2024-07-31 19:49:09 UTC"},
    %{date_str: "2024-07-31 18:49:09 UTC"},
    %{date_str: "2024-07-31 17:49:09 UTC"},
    %{date_str: "2024-07-31 16:49:09 UTC"},
    %{date_str: "2024-07-31 15:49:09 UTC"},
    %{date_str: "2024-07-31 14:49:09 UTC"},
    %{date_str: "2024-07-31 13:49:09 UTC"},
    %{date_str: "2024-07-31 12:49:09 UTC"},
    %{date_str: "2024-07-31 11:49:09 UTC"},
    %{date_str: "2024-07-31 10:49:09 UTC"},
    %{date_str: "2024-07-31 09:49:09 UTC"},
    %{date_str: "2024-07-31 08:49:09 UTC"},
    %{date_str: "2024-07-31 07:49:09 UTC"},
    %{date_str: "2024-07-31 06:49:09 UTC"},
    %{date_str: "2024-07-31 05:49:09 UTC"},
    %{date_str: "2024-07-31 04:49:09 UTC"},
    %{date_str: "2024-07-31 03:49:09 UTC"},
    %{date_str: "2024-07-31 02:49:09 UTC"},
    %{date_str: "2024-07-31 01:49:09 UTC"},
    %{date_str: "2024-07-31 00:49:09 UTC"},
    %{date_str: "2024-07-30 23:49:09 UTC"},
    %{date_str: "2024-07-30 22:49:09 UTC"},
    %{date_str: "2024-07-30 21:49:09 UTC"},
    %{date_str: "2024-07-30 20:49:09 UTC"},
    %{date_str: "2024-07-30 19:49:09 UTC"},
    %{date_str: "2024-07-30 18:49:09 UTC"},
    %{date_str: "2024-07-30 17:49:09 UTC"},
    %{date_str: "2024-07-30 16:49:09 UTC"},
    %{date_str: "2024-07-30 15:49:09 UTC"},
    %{date_str: "2024-07-30 14:49:09 UTC"},
    %{date_str: "2024-07-30 13:49:09 UTC"},
    %{date_str: "2024-07-30 12:49:09 UTC"},
    %{date_str: "2024-07-30 11:49:09 UTC"},
    %{date_str: "2024-07-30 10:49:09 UTC"},
    %{date_str: "2024-07-30 09:49:09 UTC"},
    %{date_str: "2024-07-30 08:49:09 UTC"},
    %{date_str: "2024-07-30 07:49:09 UTC"},
    %{date_str: "2024-07-30 06:49:09 UTC"},
    %{date_str: "2024-07-30 05:49:09 UTC"}
  ]
]
2

I believe you need to tell it what time zone it is in then what timezone you want, e.g.

SELECT x.eventtime AT TIME ZONE 'UTC' AT TIME ZONE 'AEST' AS eventime

3

this code:

“hour” →
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(range.end_date, range.start_date)}
),
select: %{
date_str:
fragment(
“to_char(? AT TIME ZONE ?,‘YYYY-MM-DD HH24:MI:SS TZ’)”,
c.entry,
^timezone
)
}
)

Has this result:

…[test/cogito/reports/data_source/data_source_sample_test.exs:58: Cogito.Reports.DataSource.DataSourceSampleTest.“test Test Sample Series - NO Calls Test Sample Time Series Hour”/1]
[data: data] #=> [
data: [
%{date_str: "2024-08-01 13:44:40 "},
%{date_str: "2024-08-01 12:44:40 "},
%{date_str: "2024-08-01 11:44:40 "},
%{date_str: "2024-08-01 10:44:40 "},
%{date_str: "2024-08-01 09:44:40 "},
%{date_str: "2024-08-01 08:44:40 "},
%{date_str: "2024-08-01 07:44:40 "},
%{date_str: "2024-08-01 06:44:40 "},
%{date_str: "2024-08-01 05:44:40 "},
%{date_str: "2024-08-01 04:44:40 "},
%{date_str: "2024-08-01 03:44:40 "},
%{date_str: "2024-08-01 02:44:40 "},
%{date_str: "2024-08-01 01:44:40 "},
%{date_str: "2024-08-01 00:44:40 "},
%{date_str: "2024-07-31 23:44:40 "},
%{date_str: "2024-07-31 22:44:40 "},
%{date_str: "2024-07-31 21:44:40 "},
%{date_str: "2024-07-31 20:44:40 "},
%{date_str: "2024-07-31 19:44:40 "},
%{date_str: "2024-07-31 18:44:40 "},
%{date_str: "2024-07-31 17:44:40 "},
%{date_str: "2024-07-31 16:44:40 "},
%{date_str: "2024-07-31 15:44:40 "},
%{date_str: "2024-07-31 14:44:40 "},
%{date_str: "2024-07-31 13:44:40 "},
%{date_str: "2024-07-31 12:44:40 "},
%{date_str: "2024-07-31 11:44:40 "},
%{date_str: "2024-07-31 10:44:40 "},
%{date_str: "2024-07-31 09:44:40 "},
%{date_str: "2024-07-31 08:44:40 "},
%{date_str: "2024-07-31 07:44:40 "},
%{date_str: "2024-07-31 06:44:40 "},
%{date_str: "2024-07-31 05:44:40 "},
%{date_str: "2024-07-31 04:44:40 "},
%{date_str: "2024-07-31 03:44:40 "},
%{date_str: "2024-07-31 02:44:40 "},
%{date_str: "2024-07-31 01:44:40 "},
%{date_str: "2024-07-31 00:44:40 "},
%{date_str: "2024-07-30 23:44:40 "},
%{date_str: "2024-07-30 22:44:40 "},
%{date_str: "2024-07-30 21:44:40 "},
%{date_str: "2024-07-30 20:44:40 "},
%{date_str: "2024-07-30 19:44:40 "},
%{date_str: "2024-07-30 18:44:40 "},
%{date_str: "2024-07-30 17:44:40 "},
%{date_str: "2024-07-30 16:44:40 "},
%{date_str: "2024-07-30 15:44:40 "},
%{date_str: "2024-07-30 14:44:40 "}
]
] I consider that progress since it is no longer UTC :slight_smile:

4

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
                )
            }
          )
                                                       )