Handling dates properly is delicate business and, thankfully, Postgres gives you many tools to help make sure you store date information correctly. But that only works if you know what's going on!
We’ve all had to deal with storing date information in a database, and I’m sure most of us follow the guidance of “just use UTC” - which I think is great advice for the most part. Until it isn’t.
But when would it not be the right thing to do? This, unfortunately, is something you only figure out when your ass is on fire… let me explain.
When I ran Tekpub.com back in 2012, we had been in business for 4 years and our customer base was starting to take off. This, of course, was exciting so, for the holiday season, I put on a big Holiday Sale at 50% off.
I did not expect the response! We had a six-figure sales month. This blew my mind completely! I was excited for our business, of course, but also very happy for the authors earning royalties from us - they were in for a nice bump.
Anyway, it was great fun until I got a call from my accountant when he was trying to reconcile my books with my bank statements. We were off by a significant amount.
A week later I was able to unsnarl my books, and I learned a valuable lesson when it comes to storing dates and times.
Here’s the thing: storing dates as UTC only works if you know how to pull those dates back out properly, and have them mean something. The platform I was using at the time stored date values as timestamp in Postgres, which is a UTC time stamp without a time zone designation. Sounds good, right?
Unfortunately, reading those values back out causes all kinds of pain unless, of course, your business is located in England near GMT. Consider the following:
timestamp date field as such.at time zone 'America/New_York' to your query to cast your date to east coast time.The answer to this problem is that Postgres (and I’m sure other platforms too) is very literal when it comes to storing dates at UTC using timestamp. Postgres thinks it literally happened at that time, GMT. When you ask for a conversion to EST from GMT, it will give it to you.
In our case, the date stored is incorrect and off by 8 or so hours. Ouch. This would throw off every sales report, which might be a small amount and not matter so much in sales meetings - but it matters to book keepers and accountants!
We could, of course, store dates using timestamptz, which is a time stamp with a time zone offset. If I had done that, I would have been able to read the dates back out with the correct time stamp.
Maybe.
See this only works if you know what time zone your server is in. Do you? When working locally and using local Postgres, your server’s time zone is the same as your local machine, which is wherever you’re located in the world.
If your database is in the cloud, however, that’s not the case, and it’s likely your server is set to UTC, even if your data center is located at us-west, us-east, asia, or whatever. This is true for AWS, GCP, Azure and Digital Ocean. Might be worth confirming yours…
The worst part, however, is that you only realize the mess you’re in when it comes time to generate reports, specifically sales reports, and the sales differ from your bank.
So, what should you do then? Good question. The first step is to be aware of the problem. The second step is to store the dates with the right offset for your business.
That’s the subject of this week’s video - dates and date handling in Postgres. Enjoy!