How to accept timezone in Postgres database using Django

Question

I have a model

models.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models

from django.utils import timezone

class Article(models.Model):
     sort = models.IntegerField(blank=True)
     pub_date = models.DateTimeField(default=timezone.now)
     title = models.CharField(max_length=30, blank=True)

common.py

 TIME_ZONE = 'America/New_York'
 USE_TZ = True

local.py

 from .common import *

 CELERY_BROKER_URL = env('REDIS_URL')
 CELERY_RESULT_BACKEND = env('REDIS_URL')

 CELERY_ACCEPT_CONTENT = ['application/json']
 CELERY_TASK_SERIALIZER = 'json'
 CELERY_RESULT_SERIALIZER = 'json'
 CELERY_TIMEZONE = TIME_ZONE

production.py

 from .common import *

 CELERY_BROKER_URL = env('REDIS_URL')
 CELERY_RESULT_BACKEND = env('REDIS_URL')

 CELERY_ACCEPT_CONTENT = ['application/json']
 CELERY_TASK_SERIALIZER = 'json'
 CELERY_RESULT_SERIALIZER = 'json'
 CELERY_TIMEZONE = TIME_ZONE

tasks.py

 from __future__ import absolute_import, unicode_literals
 from celery.decorators import task

 from .models import Article

 import urllib2
 import json
 import datetime

 @task(name="articles")
 def update_article():

 # .... more code
 article = Article.objects.get(id=1)
       if article != None:
            article.pub_date = datetime.datetime.now()
            article.title = "Hello"
            article.save()

When I run in the django shell

 import datetime
 pub_date = datetime.datetime.now()
 print pub_date

The pub_date is EST / 'America/New_York' timezone - correct.

I have a celery to update the Article, I wrote a simple code

article.pub_date = datetime.datetime.now()

When the pub_date is updated, in the DB the date is in UTC not America/New_York / EST timezone, even the shell is showing me correct ETC, but running it with the task, in the postgres DB it is UTC

I found it, so my CELERY_TIMEZONE = TIME_ZONE and my TIME_ZONE = 'America/New_York' in settings — Radek, May 02 '17 at 22:34
http://stackoverflow.com/questions/22786748/celery-scheduled-tasks-problems-with-timezone http://docs.celeryproject.org/en/latest/userguide/periodic-tasks.html Maybe you need to reset your database scheduler? — Denise Mauldin, May 02 '17 at 22:39
I have restarted all services and I set it to be CELERY_ACCEPT_CONTENT = ['application/json'] CELERY_TASK_SERIALIZER = 'json' CELERY_RESULT_SERIALIZER = 'json' CELERY_TIMEZONE = 'America/New_York' — Radek, May 02 '17 at 22:44
I have updated settings.py with more settings, still not working, still UTC — Radek, May 02 '17 at 22:46
Did you do: python manage.py shell >>> from djcelery.models import PeriodicTask >>> PeriodicTask.objects.update(last_run_at=None) — Denise Mauldin, May 02 '17 at 22:53
Nope but if I do that it says ImportError: No module named djcelery.models - not sure how should I reference that where is the celery model...I know I have celery in the installed apps — Radek, May 02 '17 at 23:08
always use `django.utils.timezone.now()` instead of `datetime.datetime.now()` — alfonso.kim, May 03 '17 at 13:34
@alfonso.kim That change started working on my local environment, but when I have pushed it to heroku no change, even if I did heroku restart, I have updated the question with local and production settings, but they are the same not sure why heroku postgres is showing it in different TZ — Radek, May 03 '17 at 14:14
Directly in postgres you will always see the UTC time, and Django will translate it to the desired timezone. Also make sure that in production the column is created with `timestamp with time zone` — alfonso.kim, May 03 '17 at 14:40
@alfonso.kim how can I created a column with timestamp with time zone? Can you post and an answer that I can comment on that one? — Radek, May 03 '17 at 14:49

score 2 · Accepted Answer · answered May 03 '17 at 15:17

2

Following the comments =)

Always use django.utils.timezone.now() instead of datetime.datetime.now().

Please note that directly in Postgres you will see these datetime columns in UTC + TZ offset format, and Django will translate the datetime to the desired timezone (either server TZ or the client TZ if the time is rendered in a view).

Now, if in your local Django shell you see the correct datetime but not in Heroku, it may be that Heroku the column does not hold TZ information (althought I find this hard to belive). I haven't had used Heroku but the Django migrations will take care of a timestamp with time zone column. In postgres you can check the type of a column via psql (or dbshell):

\d <your table>

                   Table article
    Column     |           Type            |        Modifiers
---------------+---------------------------+---------------------
 id            | integer                   | ...
 pub_date      | timestamp with time zone  | ...
 ....          | ...                       | ...

Hope this helps.

answered May 03 '17 at 15:17

alfonso.kim

2,844
4
32
37

Yes in heroku it shows me pub_date | timestamp with time zone | not null, example 2017-05-03 14:53:00.361049+00 - but this time is 4 hours ahead that I needed to be, local shows 10:53 and heroku 14:53, that is what is strange to me, postgres on local should have same features like heroku postgres so not sure why it works on local and not heroku. – Radek May 03 '17 at 15:23
It is OK if you see `14:53:00.361049+00`, since NY is UTC-4. Where are you seeing 14:53? in Heroku? In postgres? in `shell`? `dbshell`? in a model? Somewhere the datetime is show naive instead of tz aware. – alfonso.kim May 03 '17 at 15:31
I am connected to Postgres to local via CLI and doing a simple select * on the table to see all the data, on the local I see 10:53:00.... and on heroku I see with heroku pg:psql select * - 14:53:00.361049+00 - same environment, same DB why different times? – Radek May 03 '17 at 15:32
a naive datetime of `10:53:00` is the same that a tz aware `14:53:00.361049+00`. Note that the rows you saved with `article.pub_date = datetime.datetime.now()` will hold a different time zone that the ones you saved with `article.pub_date = timezone.now()` – alfonso.kim May 03 '17 at 15:39
No I am not doing datetime.datetime.now() on any of them I have local and heroku article.pub_date = timezone.now() both, local shows 2017-05-03 10:45:00.836694-04 and heroku 14:55:00.361049+00 - same code, different result – Radek May 03 '17 at 15:48
Ah, you have the answer! note the suffix `-04` in your local datetime and the suffix `+00` in Heroku. The first is telling you that the datetime has an offset of ´-4´ and the second is telling that is in UTC time. If you print `an_article.pub_date` in django you should see the correct time. – alfonso.kim May 03 '17 at 15:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/143311/discussion-between-rentgeeen-and-alfonso-kim). – Radek May 03 '17 at 16:00
Could not figure why I am not seeing TZ in the DB just like on the local, but when I am reading the date and time and displaying on the template it is EST correct on heroku. I marked it as answered, but best would be to find out how to do it in the DB entirely, thank you :) – Radek May 05 '17 at 02:47
To make sure I ran this command: `heroku config:add TZ="America/New_York"` – Radek May 05 '17 at 02:53
Glad it worked, at the end those are the same datetime, only one is in UTC and the other in EST. Maybe [this](https://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-TIMEZONE) can help. – alfonso.kim May 05 '17 at 13:28
That is what I have checked first, but not sure how to set on heroku their DBs to be EST directly. – Radek May 05 '17 at 22:55

How to accept timezone in Postgres database using Django

1 Answers1