474

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age.

The problem I'm having is that both datetime.datetime.now() and datetime.datetime.utcnow() seem to return timezone unaware timestamps, which results in me getting this error:

TypeError: can't subtract offset-naive and offset-aware datetimes

Is there a way to avoid this (preferably without a third-party module being used).

EDIT: Thanks for the suggestions, however trying to adjust the timezone seems to give me errors.. so I'm just going to use timezone unaware timestamps in PG and always insert using:

NOW() AT TIME ZONE 'UTC'

That way all my timestamps are UTC by default (even though it's more annoying to do this).

Asclepius
  • 57,944
  • 17
  • 167
  • 143
Ian
  • 24,116
  • 22
  • 58
  • 96

12 Answers12

455

have you tried to remove the timezone awareness?

from http://pytz.sourceforge.net/

naive = dt.replace(tzinfo=None)

may have to add time zone conversion as well.

edit: Please be aware the age of this answer. An answer involving ADDing the timezone info instead of removing it in python 3 is below. https://stackoverflow.com/a/25662061/93380

phillc
  • 7,137
  • 1
  • 22
  • 15
  • 41
    This seems to be the only way to do it. Seems pretty lame that python's got such crappy support for timezones that it needs a third-party module to work with timestamps properly.. – Ian Apr 28 '09 at 04:24
  • 38
    (Just for the record) Actually adding information about time zone may be a better idea: http://stackoverflow.com/a/4530166/548696 – Tadeck Jun 21 '12 at 04:59
  • 9
    naive datetime objects are inherently ambiguous and therefore they should be avoided. It is [easy to add tzinfo instead](http://stackoverflow.com/a/25662061/4279) – jfs Sep 04 '14 at 09:39
  • @J.F.Sebastian: you say `naive datetime objects are inherently ambiguous`, but not necessarily... if I call datetime.datetime.utcnow(), that's UTC and therefore should be completely unambiguous... yet it returns a naive datetime object. – Kylotan Jun 04 '15 at 17:41
  • @Kylotan: given a naive datetime object, how do you know what timezone should you use? Otherwise, following your logic even naive datetime objects representing local time are unambiguous (all you need is to provide the corresponding UTC offset, to disambiguate the time). – jfs Jun 04 '15 at 19:26
  • 3
    UTC doesn't need a timezone, by definition. If the other datetime object is timezone aware then surely that provides the offset relative to UTC. UTC times may not have a timezone but they are not 'naive' and Python is handling this wrongly. – Kylotan Jun 05 '15 at 10:23
  • 2
    @Kylotan: UTC is a timezone in this context (as represented by tzinfo class). Look at `datetime.timezone.utc` or `pytz.utc`. For example, `1970-01-01 00:00:00` is ambiguous and you have to add a timezone to disambiguate: `1970-01-01 00:00:00 UTC`. You see, you have to *add* new information; the timestamp by itself is ambiguous. – jfs Jun 06 '15 at 01:22
  • 1
    @J.F.Sebastian: The issue is that `utcnow` should not be returning a naive object or a timestamp without a timezone. From the docs, "An aware object is used to represent a specific moment in time that is not open to interpretation". Any time in UTC meets this criterion, by definition. – Kylotan Jun 06 '15 at 08:54
  • Adding information for this particular use case seems pretty safe: `datetime.utcnow().replace(tzinfo=dateutil.tz.tzutc())`. It should produce a UTC datetime object with the utc timezone set. I'm not sure why `datetime.utcnow()` doesn't do that by default... – Luiz C. Nov 16 '17 at 20:33
  • @Kylotan: any time in any explicit given timezone meets this criterion (using your own words), not just UTC. My point, is that a naive datetime object (by itself i.e., without *additional* time zone info) is *ambiguous*. I'll repeat my self: you have to add the time zone info to disambiguate e.g., `naive_datetime.timestamp()` interprets the `naive_datetime` object as a local time i.e., it would be **wrong** to pass it the returned value from utcnow(). `dt = datetime.now(timezone.utc)` solution doesn't have this problem: `dt.timestamp()` uses correct (UTC) time zone. – jfs Oct 25 '20 at 08:13
  • @jfs You seem to be arguing against my first comment regarding the definition of 'naive' and that was not well-worded on my part. Please read my most recent comment for all I have to say on the matter, which is that returning a "naive" object from "utcnow" is a flaw in Python. – Kylotan Oct 25 '20 at 15:50
  • 1
    @Kylotan we agree that time zone aware datetime should be used in this case. – jfs Oct 25 '20 at 16:38
400

The correct solution is to add the timezone info e.g., to get the current time as an aware datetime object in Python 3:

from datetime import datetime, timezone

now = datetime.now(timezone.utc)

On older Python versions, you could define the utc tzinfo object yourself (example from datetime docs):

from datetime import tzinfo, timedelta, datetime

ZERO = timedelta(0)

class UTC(tzinfo):
  def utcoffset(self, dt):
    return ZERO
  def tzname(self, dt):
    return "UTC"
  def dst(self, dt):
    return ZERO

utc = UTC()

then:

now = datetime.now(utc)
jfs
  • 399,953
  • 195
  • 994
  • 1,670
  • 18
    Better than removing the tz as the accepted answer advocates IMHO. – Shautieh Jun 01 '17 at 05:11
  • 6
    Here is a list of python timezones: https://stackoverflow.com/questions/13866926/is-there-a-list-of-pytz-timezones#13867319 – atlas_scoffed May 09 '18 at 10:11
  • To add a timezone to a whole Pandas column/series rather than a single item, try something like: `df['time'] = df['time'].dt.tz_localize(timezone.utc)` – Dave X Aug 01 '22 at 02:14
78

I know some people use Django specifically as an interface to abstract this type of database interaction. Django provides utilities that can be used for this:

from django.utils import timezone
now_aware = timezone.now()

You do need to set up a basic Django settings infrastructure, even if you are just using this type of interface (in settings, you need to include USE_TZ=True to get an aware datetime).

By itself, this is probably nowhere near enough to motivate you to use Django as an interface, but there are many other perks. On the other hand, if you stumbled here because you were mangling your Django app (as I did), then perhaps this helps...

sage
  • 4,863
  • 2
  • 44
  • 47
  • 2
    you need `USE_TZ=True`, to get an aware datetime here. – jfs Oct 04 '15 at 07:41
  • 2
    Yes - I forgot to mention that you need to set up your settings.py as J.F.Sebastian describes (I guess this was an instance of 'set and forget'). – sage Oct 04 '15 at 22:39
  • And this can also be easily converted to other timezones, such as `+ timedelta(hours=5, minutes=30)` for IST – ABcDexter May 10 '20 at 11:23
45

This is a very simple and clear solution
Two lines of code

# First we obtain de timezone info o some datatime variable    

tz_info = your_timezone_aware_variable.tzinfo

# Now we can subtract two variables using the same time zone info
# For instance
# Lets obtain the Now() datetime but for the tz_info we got before

diff = datetime.datetime.now(tz_info)-your_timezone_aware_variable

Conclusion: You must mange your datetime variables with the same time info

ePi272314
  • 12,557
  • 5
  • 50
  • 36
  • Incorrect? The code I wrote was tested and I am using it in a django project. It's much much clear and simple – ePi272314 Oct 04 '15 at 13:47
  • the "incorrect" refers to the last sentence in your answer: *"...must add ...not UTC"* -- UTC timezone works here and therefore the statement is incorrect. – jfs Oct 04 '15 at 13:53
  • 1
    to be clear I meant: `diff = datetime.now(timezone.utc) - your_timezone_aware_variable` works (and the `(a - b)` formula above is the explanation why `(a - b)` can work even if `a.tzinfo` is not `b.tzinfo`). – jfs Nov 07 '16 at 14:39
22

You don't need anything outside the std libs

datetime.datetime.now().astimezone()

If you just replace the timezone it will not adjust the time. If your system is already UTC then .replace(tz='UTC') is fine.

>>> x=datetime.datetime.now()
datetime.datetime(2020, 11, 16, 7, 57, 5, 364576)

>>> print(x)
2020-11-16 07:57:05.364576

>>> print(x.astimezone()) 
2020-11-16 07:57:05.364576-07:00

>>> print(x.replace(tzinfo=datetime.timezone.utc)) # wrong
2020-11-16 07:57:05.364576+00:00
JeffCharter
  • 1,431
  • 17
  • 27
11

I also faced the same problem. Then I found a solution after a lot of searching .

The problem was that when we get the datetime object from model or form it is offset aware and if we get the time by system it is offset naive.

So what I did is I got the current time using timezone.now() and import the timezone by from django.utils import timezone and put the USE_TZ = True in your project settings file.

Ashok Joshi
  • 428
  • 4
  • 13
11

The most straight forward answer to the question in the title seems to be missing:

naive_date.replace(tzinfo=aware_date.tzinfo) - aware_date
Dave
  • 1,569
  • 10
  • 19
6

The psycopg2 module has its own timezone definitions, so I ended up writing my own wrapper around utcnow:

def pg_utcnow():
    import psycopg2
    return datetime.utcnow().replace(
        tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))

and just use pg_utcnow whenever you need the current time to compare against a PostgreSQL timestamptz

erjiang
  • 44,417
  • 10
  • 64
  • 100
  • Any tzinfo object that returns zero utc offset will do, [for example](http://stackoverflow.com/a/25662061/4279). – jfs Sep 04 '14 at 09:38
6

I came up with an ultra-simple solution:

import datetime

def calcEpochSec(dt):
    epochZero = datetime.datetime(1970,1,1,tzinfo = dt.tzinfo)
    return (dt - epochZero).total_seconds()

It works with both timezone-aware and timezone-naive datetime values. And no additional libraries or database workarounds are required.

John L. Stanley
  • 71
  • 1
  • 6
2

I've found timezone.make_aware(datetime.datetime.now()) is helpful in django (I'm on 1.9.1). Unfortunately you can't simply make a datetime object offset-aware, then timetz() it. You have to make a datetime and make comparisons based on that.

Joseph Coco
  • 404
  • 6
  • 11
1

I know this is old, but just thought I would add my solution just in case someone finds it useful.

I wanted to compare the local naive datetime with an aware datetime from a timeserver. I basically created a new naive datetime object using the aware datetime object. It's a bit of a hack and doesn't look very pretty but gets the job done.

import ntplib
import datetime
from datetime import timezone

def utc_to_local(utc_dt):
    return utc_dt.replace(tzinfo=timezone.utc).astimezone(tz=None)    

try:
    ntpt = ntplib.NTPClient()
    response = ntpt.request('pool.ntp.org')
    date = utc_to_local(datetime.datetime.utcfromtimestamp(response.tx_time))
    sysdate = datetime.datetime.now()

...here comes the fudge...

    temp_date = datetime.datetime(int(str(date)[:4]),int(str(date)[5:7]),int(str(date)[8:10]),int(str(date)[11:13]),int(str(date)[14:16]),int(str(date)[17:19]))
    dt_delta = temp_date-sysdate
except Exception:
    print('Something went wrong :-(')
I_do_python
  • 1,366
  • 4
  • 16
  • 31
  • FYI, [`utc_to_local()` from my answer](http://stackoverflow.com/a/13287083/4279) returns local time as an **aware** datetime object (It is Python 3.3+ code) – jfs Sep 04 '14 at 09:26
  • It is not clear what your code is trying to do. You could replace it with `delta = response.tx_time - time.time()`. – jfs Sep 04 '14 at 09:28
1

Is there some pressing reason why you can't handle the age calculation in PostgreSQL itself? Something like

select *, age(timeStampField) as timeStampAge from myTable
Nic Gibson
  • 7,051
  • 4
  • 31
  • 40
  • 3
    Yes there is.. but I was mostly asking because I want to avoid doing all the calculations in postgre. – Ian Apr 28 '09 at 12:27