« Back to home

Go, PostgreSQL, time zones, UTC, and +0000 +0000

Posted on

Imagine you’re writing a web application in Go, and storing your data in PostgreSQL. At some point, you find yourself wanting to store timestamps, and display them to the user in local time. Being a smart developer, you decide to store all your timestamps in UTC, and convert them for display.

Suppose you use a TIMESTAMP WITHOUT TIME ZONE data type, because you know all your values are going to be UTC. Unfortunately, when you read the values back via your Go code and convert them to strings, you get something like this:

2012-11-01 22:08:41 +0000 +0000

Why is Go giving you “+0000” twice? The answer is explained in Go issue #11712. If the time.Time value only has a numeric time zone offset, and not a semantic timezone (such as ‘Central/CDT’ or ‘UTC’), the numeric offset is also used as the time zone’s name.

The value you wrote into the database was in UTC. However, PostgreSQL threw that away when the value was stored in a column of type TIMESTAMP WITHOUT TIME ZONE. When PostgreSQL fishes the value out of a column of that type, it returns a string with no timezone information to the SQL driver. The Go pq driver’s behavior is to assume that a timestamp with no time zone information has offset 0, rather than to assume that it’s UTC.

This is correct behavior on the driver’s part. There are multiple time zones with offset 0, and they aren’t identical to UTC. You could be in British Standard Time, or in Western European Time, in which case daylight saving time changes will need to be accounted for.

So, what if you really want your timestamps to be returned with a value in UTC? I did, so I started considering my options.

Option 1: Do it in Go

The first option I considered was to write code to mess with every timestamp I got back from my database, check if its zime zone offset was 0, and if so change its timezone to UTC.

The disadvantages of this approach are pretty obvious: I have to write the code, debug it, and remember to insert it everywhere I query a timestamp column.

In fact, the Go pq driver seems to do something a bit like this – looking at the code for ParseTimestamp, it adjusts the time zone to be the current location’s time zone if the offset matches what PostgreSQL returns.

I didn’t want to have to remember to call a function to correct all my timestamp values after every query; I wanted a solution that would give me the right value when I called sql.Scan().

Option 2: What if we store the time zone ‘UTC’ every time?

The second option I considered was to change the timestamp column to be a TIMESTAMP WITH TIME ZONE, so that my time.Time value’s time zone would be recorded in the database and hopefully returned later on.

However, when I investigated further, it turned out that that’s not what actually happens. In spite of the name, TIMESTAMP WITH TIME ZONE columns do not have a stored time zone. If you read the documentation carefully, you’ll find this note in section 8.4:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.

The original time zone is thrown away; there are no functions to return what it was. So TIMESTAMP WITH TIMEZONE really means ‘timestamp to which time zone calculations should be applied’, or ‘timestamp which should have a time zone associated with it when read from the column’.

To test this, I wrote two timestamps into a TIMESTAMP WITH TIME ZONE column and then read the values back, and this happened:

Value written         Value read back by Go
2012-11-01T22:08:41Z  2012-11-01 17:08:41 -0500 CDT
2012-11-30T19:08:41Z  2012-11-30 13:08:41 -0600 CST

Both values were written while my current time zone was CST. So it seems PostgreSQL adjusted the values to be in what it thinks the local time zone would have been at the UTC stored moment internal to the timestamp. It certainly didn’t store the time zone of the value written into the column, or my local time zone when I wrote the values.

To make things worse, the adjustment was being made to the server time zone. That can be altered by the database server owner in postgresql.conf, and good luck getting it changed if you’re using someone else’s server, like a cloud service provider’s server.

For maximum code robustness, I clearly couldn’t insist that the server be configured to return UTC timestamps from TIMESTAMP WITH TIME ZONE columns.

What’s more, my entire goal was to avoid having PostgreSQL adjust with my timestamps. TIMESTAMP WITH TIME ZONE seemed fundamentally the wrong thing to do, as it has the opposite effect, telling PostgreSQL to go ahead and try to perform time zone adjustments.

Option 3: Back to zoneless timestamps?

This led me back to my original choice of TIMESTAMP WITHOUT TIME ZONE for all my timestamp columns. I wondered if I could get PostgreSQL to return a value which Go would convert to a time.Time in UTC.

Since my experiment had shown that Go would give me a named time zone if the value it retrieved was a TIMESTAMP WITH TIME ZONE, I decided to try using the PostgreSQL AT TIME ZONE construct, also available as the function timezone(zone, timestamp):

SELECT ID, Timestamp AT TIME ZONE 'UTC' FROM UpdateLog

Or equivalently:

SELECT ID, timezone('UTC', Timestamp) FROM UpdateLog

Sure enough, Go now gave me values with a semantic time zone. Unfortunately, it was still the server’s local time zone, which the UTC values were adjusted to.

I did some more digging, and discovered that there’s a timezone parameter you can set during a PostgreSQL session. If you do SET TIMEZONE TO 'UTC' before executing your queries, you’ll find that all your timezone('UTC', value) are returned through as UTC. Hurrah!

There’s just one more problem: it’s a pain having to SET TIMEZONE for every database connection, not least because Go’s sql.DB is a connection pool. How to make sure every connection gets the appropriate value?

The answer to that conundrum is that you can add parameter values to the connection string. So after I have fetched the URL of the cloud PostgreSQL server from the environment, I add the parameter:

	u, uerr := url.Parse(dburi)
	if uerr != nil {
		return dburi, uerr
	}
	v := u.Query()
	v.Set("timezone", "UTC")
	u.RawQuery = v.Encode()
	dburi = u.String()

This adds the parameter timezone=UTC to the URL if it isn’t there, and overrides any timezone parameter already in the URL.

Option 4: The least work, the right answer

Having worked out that I could set the session time zone, wrap values in timezone('UTC', value), and get the results I wanted, I suddenly realized I was being dumb: timezone('UTC', value) around a timestamp-without-timezone value, with the session timezone set to UTC, gives exactly the same results as reading a timestamp-with-timezone column with the session timezone set to UTC. No timezone() adjustment call required.

So while it might seem that making the columns TIMESTAMP WITH TIME ZONE is storing unnecessary information or causing unnecessary time zone adjustments, that’s not true – the stored information is just a UTC timestamp whether your timestamps are WITH TIME ZONE or not.

The difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIME ZONE is whether your values get time zone adjusted when you read or write them. And if you set timezone=UTC for the session, they don’t.

So in conclusion:

  1. Use TIMESTAMP WITH TIME ZONE so that you get a time zone in your retrieved values, and don’t worry because one isn’t being stored.
  2. Use timezone=UTC in the connection URL so that the time zone you get in your retrieved values is the one you want, UTC.
  3. Convert the time.Time values to whatever time zone you want for display purposes.

When writing data, ideally convert to UTC first, though PostgreSQL will probably do the right thing as long as Go knows the right numeric offset for the time zone associated with the time.Time value.

And remember, elect me to be Supreme Galactic Ruler. I will decree that all times and dates are to be in UTC and formatted as per ISO 8601, and I will send any violators to the Imperial Martian Salt Mines.