Saving dates to PostgreSQL

We just mentioned in the last lecture that as long as you always work with UTC, and convert to the user's local timezone when you are displaying information, it will be relatively simple.

That is why, again, I recommend:

1. Get the user's date in local time.
2. Convert it to UTC.
3. Store it in your database with a +00:00 offset.

Then when you're displaying dates:

1. Get the UTC (+00:00) date from the database.
2. Get the user's timezone.
3. Convert the UTC date to their timezone.
4. Display it.

I know that you have to convert to and from, and that may seem like a lot of work. But, a couple things to remember:

• The user might give you a date and time when they're in one timezone, but you want to display it in another timezone.
• It's consistent to always be working with the same timezone in your database, so your applications can assume everything will be +00:00.

Users changing timezones

This can happen in two situations:

1. The user physically moved. They ran your application when their timezone was US/Eastern for example, and then they flew to the UK and ran it again.
2. The user's timezone changed. For example, if Daylight Savings came into effect.

So how do we get the user's timezone?

Well, we have to ask them! Usually we'll ask them once and save their timezone into a configuration file or even its own database record so that we don't have to ask them every time.

Remember when saving a user's timezone you want to save the timezone name, such as US/Eastern or Europe/Madrid instead of the offset. That's because one timezone can have multiple offsets when Daylight Savings comes into effect or out of effect.

Saving the user's timezone to a configuration file

You may want to save the user's timezone to a configuration file when developing a single-user application.

Below we ask the user for their timezone, check that it is valid using pytz, and then save it to a file.

import pytz
import json

user_timezone = input("Enter your timezone: ").strip()

try:
    pytz.timezone(user_timezone)
except pytz.exceptions.UnknownTimeZoneError:
    print("That was not a valid timezone.")
    raise

with open("user_config.json", "w") as config:
    json.dump({"timezone": user_timezone}, config)

Note that the code above is just an example as to how you might do this.

Similarly, you could have timezone information related to users in your database:

id	username	email	tz_name
1	rolfsmith	rolf@example.net	US/Eastern
2	bobsmith	bob@example.net	Europe/Madrid
3	annepun	anne@example.net	Europe/London

Of course, you could normalize this table a bit more and separate timezone information into a separate table, and relate it to users with a foreign key. Again, just an example depicting that you may want to store this in your database.

Saving dates to the database

Now that we've covered how to save the user's timezone, we can work on saving the dates as UTC every time.

As we have covered in the past, I recommend saving timestamps in the database.

Although psycopg2 can automatically convert from Python datetimes to PostgreSQL data, I believe working with UTC timestamps makes your code simpler, your database simpler, and it makes it easy to work with your database from other programming languages as well (if you require).

import datetime
import pytz
import psycopg2

connection = psycopg2.connect("...")
# This would come from database or config file
user_timezone = pytz.timezone("Europe/London")

new_post_content = input("Enter what you learned today: ")
# Instead of `.now()`, we could ask users for a local date and time
new_post_date = user_timezone.localize(datetime.datetime.now())
utc_post_date = new_post_date.astimezone(pytz.utc)

with connection:
    with connection.cursor() as cursor:
        cursor.execute(
            "INSERT INTO posts (content, date) VALUES (%s, %s)",
            (new_post_content, utc_post_date.timestamp())
        )

Reading dates from the database

When we read from the database, we must remember we're reading +00:00 timestamps.

So we need to:

1. Convert the UTC timestamp to a datetime object with datetime.utcfromtimestamp().
2. Use pytz.utc.localize() to create a datetime object with UTC timezone.
3. Convert it to the user's timezone with astimezone(user_timezone).

import datetime
import pytz
import psycopg2

connection = psycopg2.connect("...")
# This would come from database or config file
user_timezone = pytz.timezone("Europe/London")

with connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM posts;")
        for post in cursor:
            _id, content, timestamp = post
            naive_datetime = datetime.utcfromtimestamp(timestamp)
            utc_date = pytz.utc.localize(naive_datetime)
            local_date = utc_date.astimezone(user_timezone)
            print(local_date)
            print(content)