Django 1.11 - How can I ensure TruncYear to produce Zulu time

Question

I am using Django 1.11 and Postgres 9.4.

How can I ensure TruncYear to produce Zulu time (2019-10-01T00:00:00Z). I notice it creates datetime with timezone like this (2017-01-01T00:00:00+03:00)

Here is my code for the TruncYear queryset:

from django.db.models import Count
from django.db.models.functions import TruncMonth, TruncYear, TruncDay, TruncHour

tracking_in_timeseries_data = Tracking.objects.annotate(
         year=TruncYear('created_at')).values('year', 'venue').annotate(
         count=Count('employee_id', distinct = True)).order_by('year') 

>>> for exp in tracking_in_timeseries_data:
...     print(exp['year'], exp['venue'], exp['count'])

2017-01-01 00:00:00+00:00 4 1
2019-01-01 00:00:00+00:00 2 2
2019-01-01 00:00:00+00:00 3 1
2019-01-01 00:00:00+00:00 4 1
2019-01-01 00:00:00+00:00 5 1
2019-01-01 00:00:00+00:00 6 1


>>> tracking_in_timeseries_data
<QuerySet [{'venue': 4, 'year': datetime.datetime(2017, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 1}, {'venue': 2, 'year': datetime.datetime(2019, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 2}, {'venue': 3, 'year': datetime.datetime(2019, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 1}, {'venue': 4, 'year': datetime.datetime(2019, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 1}, {'venue': 5, 'year': datetime.datetime(2019, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 1}, {'venue': 6, 'year': datetime.datetime(2019, 1, 1, 0, 0, tzinfo=<UTC>), 'count': 1}]>

And if I serialize it produce this:

serializer.py

class TimeseriesYearSerializer(serializers.ModelSerializer):
    venue = VenueTSSerializer(read_only=True)
    year = serializers.DateTimeField(read_only=True)
    count = serializers.IntegerField(read_only=True)

    class Meta:
        model = Tracking
        fields = ['venue', 'year', 'count']

output:

[
    {
      "count": 1,
      "year": "2017-01-01T00:00:00+03:00",
      "venue_id": 2
    },
    {
      "count": 1,
      "year": "2018-01-01T00:00:00+03:00",
      "venue_id": 1
    },
    {
      "count": 1,
      "year": "2018-01-01T00:00:00+03:00",
      "venue_id": 2
    },
    {
      "count": 3,
      "year": "2019-01-01T00:00:00+03:00",
      "venue_id": 1
    },
    {
      "count": 3,
      "year": "2019-01-01T00:00:00+03:00",
      "venue_id": 2
    }
  ]

How can I ensure TruncYear queryset it produce a datetime strings in Zulu time like this 2019-10-01T00:00:00Z rather than with a timezone 2019-01-01T00:00:00+03:00.

UPDATE: I noticed I temporary fix this by restarting the django service.

sudo supervisorctl stop all
sudo supervisorctl start all

It was then able to produce Z time like this 2019-10-01T00:00:00Z But after few hours, it started to produce timezone timeformat like this 2017-01-01T00:00:00+03:00

I also noticed if I rebooted the server, it wont have the Z time. I have to do supervisorctl stop and start then it temporarily seems to fix it.

Here are my code snippet for the supervisor restart

/home/user/myapp/gunicorn_start.bash
/etc/supervisor/conf.d/myapp.conf
https://gist.github.com/axilaris/01525b78fcdc03071fcd34818820d7f1

This is my server version Ubuntu 16.04.3 LTS

What could be the problem and how to fix it so it consistently produce Zulu time.

Why do you need a **`DateTimeField`** field for ***year*** instead of **IntegerField**? — JPG, Dec 09 '19 at 03:00

score 6 · Accepted Answer · answered Dec 09 '19 at 03:37

6

The very first thing I noted here is, you are using serializers.DateTimeField--DRF doc field for a year representation, where the year always will be a positive integer.

Regarding the timezone issue, Django uses the TIME_ZONE settings while querying from the database. Here is the minimal SQL query in postgress that happens when you do so,

SELECT a,b,c,DATE_TRUNC('year', "tracking_table"."created_at" AT TIME ZONE 'YOUR_TIME_ZONE_VALUE') AS "year" FROM "tracking_table

But, fortunately, you can specify the target timezone (in your case, its UTC) with the help of tzinfo parameter in the TruncYear class.

Example

import pytz

Tracking.objects.annotate(year=TruncYear('created_at', tzinfo=pytz.UTC))

To show year in the json reponse, change your serializer as,

class TimeseriesYearSerializer(serializers.ModelSerializer):
    venue = VenueTSSerializer(read_only=True)
    year = serializers.IntegerField(read_only=True, source='year.year')
    count = serializers.IntegerField(read_only=True)

    class Meta:
        model = Tracking
        fields = ['venue', 'year', 'count']

References

answered Dec 09 '19 at 03:37

JPG

82,442
19
127
206

I'ved implemented this - Tracking.objects.annotate(year=TruncYear('created_at', tzinfo=pytz.UTC)). I am giving it some time if its holding up, so far its been a few hours and its holding up. will update tomorrow – Axil Dec 11 '19 at 01:05
pardon me, what you mean by *holding up*? – JPG Dec 11 '19 at 01:22
meaning this solution is working as of now. I just need to observe it a bit longer as in the previous queryset, after a day, it will appear the timezone back again. So far, it looks like its ok but I need to observe. – Axil Dec 11 '19 at 01:23
Great...let me know the results – JPG Dec 11 '19 at 02:13
yup after more than a day, its still getting Z datetime. Thanks a lot. – Axil Dec 12 '19 at 09:51

score 0 · Answer 2 · answered Dec 08 '19 at 23:24

I've checked sources of Django Rest Framework and it looks like it uses "current timezone" by default for DateTimeField but you can set it in a force manner by passing default_timezone equal to "pytz.timezone('Zulu')". This way it will return the field value in the required timezone.

I've played with that a bit and for me it works as expected. For example for Europe/Moscow for my test serializer (in my project) I have output like

{
    "id": 1,
    "title": "test 123",
    "level": 0,
    "slug": "test-123",
    "icon_code": "123",
    "image": null,
    "brand": false,
    "children": [],
    "created_at": "2019-12-09T01:55:08.438442+03:00"
  }

And for Zulu timezone I have it like

  {
    "id": 1,
    "title": "test 123",
    "level": 0,
    "slug": "test-123",
    "icon_code": "123",
    "image": null,
    "brand": false,
    "children": [],
    "created_at": "2019-12-08T22:55:08.438442Z"
  }

My test field declaration is

created_at = serializers.DateTimeField(format='iso-8601', default_timezone=pytz.timezone('Zulu'))

It seems like in your source code you override "current timezone" but don't set it to a default one or etc and that's why it works right after the server restart and stops in a few hours (when a request sets the wrong timezone). So you have 2 ways to fix it - declare your fields as I did above using the default_timezone param or find where the current timezone value is changed). The easiest solution for the case, of course, is declaring the field timezone required but in general, of course, it should work without bugs :)

More information:

Django 1.11 - How can I ensure TruncYear to produce Zulu time

2 Answers2

Example

References