CODEWITHSUNDEEP
postgresqlelixirecto
keepTrackOfYourStack
keepTrackOfYourStack

Reputation: 1255

IN elixir with Postgres, how to generate_series and convert from 'UTC' to another zone?

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"},

Upvotes: 0

Views: 45

Answers (2)

Jason
Jason

Reputation: 3179

Rule #1: Store the data in the database in UTC.

Rule #2: See Rule #1

Rule #3: Did you think we were kidding? Eyes back up top, sport.

To go back and forth to your users you might want to try https://hexdocs.pm/timex/Timex.html, convert back and forth from local time outside of the database calls. Don't make the database think about where the query is coming from. Deal with that in your form processing.

Upvotes: 0

keepTrackOfYourStack
keepTrackOfYourStack

Reputation: 1255

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

Upvotes: 0

Related Questions