how to store timezone aware datetime with python and postgresql

Question

I am trying to store timezone aware datetime with python and postgresql and there is always one issue or the other

i get the following error when trying to save a user into postgresql database

asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2021, 6, 13, 21, 12, 1... (can't subtract offset-naive and offset-aware datetimes)

here is function that saves user into database

from datetime import datetime
import pytz


def save_user(user: auth_schema.UserCreate):
    query = "INSERT INTO users VALUES (nextval('users_id_seq'), :email, :password, '', '', :created_on, :last_login, False, True, :is_superuser)"
    return database.execute(query, values={"email": user.email, "password": user.password, "created_on": datetime.now(pytz.utc), "is_superuser": False})

so how do i get rid of the error?

asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2021, 6, 13, 21, 12, 1... (can't subtract offset-naive and offset-aware datetimes)

UPDATE:

created_on column of users table is of type timestamp

Check this link - https://stackoverflow.com/questions/796008/cant-subtract-offset-naive-and-offset-aware-datetimes — tidakdiinginkan, Jun 13 '21 at 22:37
saw that link already and do not know how to solve my issue...can you help further with an answer? or what line of code to drop to solve this? thanks — uberrebu, Jun 13 '21 at 23:51
also from link none of the answers is using `pytz`, i will like to use the recommended datetime package `pytz` for this — uberrebu, Jun 13 '21 at 23:55
What is the type of the `created_on` column in the table in the database? — snakecharmerb, Jun 17 '21 at 06:55
If you want to store a time zone aware datetime the column type needs to be `timestamp with time zone` (this will also fix the error that you are getting). — snakecharmerb, Jun 19 '21 at 10:12
and what will that be from pydantic? can you expand on what i need to do and how to do it? — uberrebu, Jun 19 '21 at 10:22

score 3 · Answer 1 · answered Jun 19 '21 at 11:26

If you want to store time zone aware timestamps in Postgresql you need to store them in columns of type timestamp with time zone (or timestamptz, which is the same). Therefore the CREATE TABLE statement for the table should look like

CREATE TABLE users (
   ...
   created_on timestamp with time zone,
   ...
);

If the table already exists, the column type can be changed by executing

ALTER TABLE users ALTER COLUMN created_on TYPE timestamp with time zone;

in the psql console (backup your data before doing this).

Note that Postgresql will always store the UTC time, so when retrieving timestamps you will need to set the tzinfo attribute on datetime instances to the value that you want.

Hi, how do I add this via models.py file? models.timestamp()? — Shah Zain, Dec 29 '21 at 04:51
@ShahZaiƞ assuming you're referring to Django, I _think_ you would you a `DateTimeField`, but I haven't used Django for anything recently, so you might want to search for existing Q&As on the topic, and the Django docs on timezones. — snakecharmerb, Dec 29 '21 at 07:29

how to store timezone aware datetime with python and postgresql

1 Answers1