How to make 'generate_series' tzaware with django

Using Django
1

Dear Django experts,

I want to generate weekly intervals from a datetime that account for daylight savings times.

Example situation: The first_datetime is 2300 3 Jan UTC and localtime. Daylights savings time shift the local time forward a hour on 12 Jan. Target output: 2300 3 Jan UTC, 2300 10 Jan UTC, 2200 17 Jan UTC, …

The code that I am using currently only moves forward in week intervals, without considering timezone.

# Models
class Celebration(Models.model):
    first_datetime = models.Date
    ...

# Managers.py
...
Celebration.objects.annotate(
            start=Func(
                ExpressionWrapper(
                    F("first_datetime"), output_field=DateTimeField()
                ),  # Find first datetime.
                end,
                Value("1 week"),
                function="generate_series",
            ),
)

How do I account for daylight savings changes in the timezone?

Related answer that uses postgres directly: datetime - How do I generate a date series in PostgreSQL? - Database Administrators Stack Exchange

I also posted this on stackoverflow (postgresql - Generate timezone aware datetimes using django annotate and 'generate_series' - Stack Overflow). No replies as of yet.

2

UTC doesn’t adjust with DST - that’s a local timezone issue. If your times are UTC, then any DST adjustments would be made when you convert it to local time to be displayed.

3

Thanks for your fast response Ken. My understanding is that the display conversion is not the issue here.

The issue is that I am generating incorrect future utc datetimes. I want to generate future datestimes using postgres that take day light savings into account.

To clarify the issue further,

  • The user sets a datetime in utc. This is in a local daylight savings time of +0.
  • I generate future datetimes in utc on a weekly interval.
  • The generated future datetimes do not account for local time.

Thank you for any response.

4

is first_datetime a DateField or DatetimeField? The name makes it seem like a datetime, but the code implies it’s a date.

5

It makes no sense to adjust a UTC datetime for DST. It’s purely a localtime issue. It’s a question of how you’re using the date as a localized time where DST comes into the picture.
Acknowledged, my use of “display” was not precise. I was referring to any use of that datetime. If you’re not displaying it, then you’re comparing it or otherwise using it. It’s the usage of that datetime, when converted to localtime, where DST adjustments are made.

UTC 2021-03-14 06:30:00 is 1:30 AM EST. UTC 2021-03-14 07:30:00 becomes 3:30 AM EDT - for those areas in the US using DST in the Eastern time zone.

However, UTC 2021-03-14 06:30:00 is 12:30 AM CST. UTC 2021-03-14 07:30:00 is 1:30 AM CST, and UTC 2021-03-14 08:30:00 is 3:30 AM CDT - for those areas in the US using DST in the Central time zone.

So you can’t convert UTC to DST unless you know the target time zone, and the conversion occurs for that time zone localized time - not the UTC time.

6

Hi Tim, first_datetime is a datetime.

Here is a little more detail to clarify. The precise situation is that:

  • the user is setting the first_datetime on the 25/03/2021 the start of the day.
  • In England, day light savings begins on the 28/03/2021. This causes local time to be +1 from UTC.
  • Consequently, datetimes that I generate after day light savings begins have the wrong time.

My second attempt (writing the raw sql now)

SELECT
       timezone('Europe/London', generate_series(
               cel.first_date::date,
               '2021-04-20'::timestamptz,
               '1 week'::interval
           )) AS generated
    FROM celebration cel WHERE cel.id = 330

Output:
image

The desired output (in UTC) should be:

2021-03-25 00:00:00.000000
2021-03-31 23:00:00.000000
2021-04-07 23:00:00.000000
2021-04-14 23:00:00.000000

Thanks for your help.

7

Thanks Ken. Understood regarding your point about display.

Following your point, how would you recommend that I write a query to generate the localised times from the first_datetime? I have added the precise situation above.

8

I don’t. That sequence of values that you’ve displayed is wrong. Those are not 1-week intervals of UTC datetime values.

I recommend that the datetimes as stored in the database remain UTC. I recommend that you localize them for use in your code.

9

Thanks again Ken. I don’t quite understand your response. I might have miscommunicated the situation.

The values that I showed are not all stored in the database. They are simply a set of rows that the query generates. To get the localised values, I am trying is to write a query to generate this set as a set of localised values.

10

Ok, we weren’t quite on the same page - but that’s ok, it doesn’t materially change my response.

I need to ask the more fundamental question - why are you trying to do this in the query? What are you doing where that matters?

I don’t worry about the query returning localized times. I keep UTC in the model fields, and only localize for display. For us, all comparisons, calculations, etc are done in UTC.

11

Sure Ken. I will describe the situation.

Situation:

  • For the calendar app that I am working on, the user - who only works part time - books a day of the week that he will not work on (an Off Work Day).
  • The user books this day on Friday 26 March for two weeks. The first Off Work starts at 26/03/2021 0000 UTC and ends at 2359 UTC on the same day. (This is in England. London’s timezone (GMT) == UTC + 0)
  • The user looks at the calendar to check that he has booked for two weeks.
  • Unfortunately, because daylight savings occurs on the 28/03/2021 in England (making local time UTC + 1), the end time for his next week’s Off Work are shifted an hour later.
  • Daily savings time means that the end time of UTC 2359 is now 0059 in local time for the next day. - The end time should be 2359 local time, which would be UTC 2259.

I want to display the correct local time for each Off Work day, accounting for daylight savings changes.

Thanks again for your help.

–
As a side note, here is what the calendar looks like in the example that I have described:

image
image1054×762 53 KB

12

Ok, this seems to be the reverse situation then.
It looks to me like you’ve got the case where a user is entering a date that would be a localtime, so you would want to convert it to UTC before storing it.
For example, if March 28 is entered, the start time would be (local) 000000 to 2359. When that gets converted to UTC, it should become 0000 to 2259 in the database.
When the data is retrieved for display, localizing it would change it back to 000000 to 2359.

See the pytz module for some examples of converting datetime fields between timezones and utc.

13

Thanks Ken. Yeah, I agree. I was wondering if it is possible to generate the correct UTC datetimes using a query. Would be interested to know if you have any suggestions regarding that.

14

Nope, no suggestions on that. It has never occurred to me to bother trying anything like that. I’ve never seen the need or value of trying it. Because of the potential ambiguity of localized times, all “work” is done in UTC. Times are only localized for “human consumption” when displayed.

15

Ok. Thanks for your help Ken.

16

Looking at PostgreSQL’s docs for generate_series, it does support timestamps with timezones. I’m not sure how it handles daylight savings, so you’ll need to test that. Since Django stores datetimes as timestamp with time zone you may need to figure out how to change the time zone per record if you want to change it from the stored default (settings.TIME_ZONE). Likely via another annotation or wrapping expression. Here’s a basic primer on how to use AT TIME ZONE in PostgreSQL.