Working with Postgres Types

https://blog.jonudell.net/2021/07/27/working-with-postgres-types/

by alainchabat • 5 years ago

65 27 5 years ago

blog.jonudell.net

CRConrad• 5 years ago

Interesting read, not just this post but the whole series. I used to follow Udell's writings (though I can't recall now where they were published; must have been some magazine, I think, not just his own blog), but it seems he drifted out of my sight sometime in the last decade. Thanks for reminding me of him!

Stratoscope• 5 years ago

Agreed, I used to read Jon a lot back in the magazine days. Glad to see he is still at it!

This page will answer some of your questions:

https://blog.jonudell.net/about/

mhw• 5 years ago

He used to write for Byte magazine.

doctor_eval8• 5 years ago

I didn’t read the whole article (probably not aimed at me) but I just want to say that you should never, ever, ever use “timestamp without time zone” in PG because it is actually “clock face time” rather than “moment in time” and therefore the moment you try to do anything at all clever with time, you will start to question your own sanity. Well I did anyway.

There is not enough room here to explain fully but “Timestamp *with* time zone” effectively means UTC time which is the closest equivalent in PG to Unix epoch time and almost certainly what most people want. It doesn’t actually store a time zone.

You’ll thank me later :)

kristiandupont• 5 years ago

I've made a "linter" tool to enforce this: [link redacted]

rad_gruchalski• 5 years ago

TIL: one can write a Postgres hook to enforce this in the database at 'create table' time. This would fit nicely in the ProcessUtility hook.

quietbritishjim• 5 years ago

“Timestamp *with* time zone” ... doesn’t actually store a time zone.

Thanks, I would never have guessed that!

Fire-Dragon-DoL4• 5 years ago

Can you expand? I don't understand clock face time vs moment in time. If you have a link, I'm happy to go there.

By reading the doc, it seems tome that timestamp without timezone should be used whenever the entirety of time operations are handled by the application. I'd argue that using timestamptz might actually cause bugs due to the conversion, often overlooked because everything is configured to use UTC.

cdaringe• 5 years ago

- clock face time, lossy. non geo-aware/geo-normalized value. generally a scalar, or partial date time. value may not be robust if consumed outside of reporter’s locale

- moment in time, geo-aware. scalar + geo-value, such as TZ or UTC offset

I (mediocrely) wrote about this years ago: https://cdaringe.com/managing-application-dates

patrickthebold3• 5 years ago

Let's say your application,for whatever reason, sends 3 different timestamps to pg: 2021-08-08T05:00:00+01 2021-08-08T06:00:00+02 2021-08-08T07:00:00+03

Do you want PG to store the same value or different values?

If you answer 'same', you want with time zone.

Now your answer might be: "make sure your application doesn't do that" in which case your probably fine either way but then you need to make sure you do that correctly.

agent3272• 5 years ago

Seconded. I now store timestamps as the number of microseconds since the epoch. It may not be as fancy as an actual timestamp type, but at least it works without surprises.

mekster• 5 years ago

Except you have no idea when if you look at the database manually.

doctor_eval8• 5 years ago

I went down this path for a time and I recommended it for MySQL some time ago. But actually epoch time and timestamptz are equivalent. You can specify the number of decimals in a timestamptz to get to microsecond precision if you like.

quambene• 5 years ago

Or alternatively,

"timestamp without time zone default (now() at time zone 'utc')"

is fine as well and does make the time zone explicit in the type definition.

doctor_eval8• 5 years ago

No, this just makes the default value the UTC time at the time a row is created, but it doesn’t help.

Let’s say I create a row at “12:34 5 April UTC”. If I now change client time zones and run a query, the epoch time will change to “12:34 5 April EST”. That’s probably not what you want.

The underlying issue is that most PG functions use timestamptz, and PG silently converts timestamp to timestamptz. This conversion uses the local TZ and can lead to nondeterministic behaviour from the perspective of the SQL expression.

Put another way, timestamptz is perfectly stable, timestamp can be nondeterministic, use timestamptz unless you are absolutely sure you know what you are doing.

shanipribadi• 5 years ago

Both postgres timestamptz and timestamp are the same, they both store timestamp as UTC epoch time. Also timestamptz does not store the timezone given by the client, the only thing it stores is the UTC epoch time, so you'll lose the original timezone information. The only difference between the two is that with timestamptz postgres does a conversion between the text representation and the stored value based on a configured timezone (session, or database configured).

The decision on which type to use IMO depends a lot more on the behaviour of the database driver of your programming language, and which one works better with the type.

If you're not writing an application that uses a db driver, but interacting with pg over psql manually, timestamptz is preferrable because a lot of pg functions works better with timestamptz. If you use timestamp, you would need to be using explicit timezone conversion and be consistent to avoid weird double timezone conversion issues, which makes it far more verbose.

Working with Postgres Types

Related Stories