←back to thread

Timestamps and Time Zones in PostgreSQL (2016)

(phili.pe)
189 points GordonS | 4 comments | 30 Mar 19 21:47 UTC | HN request time: 0.001s | source
Show context
robbiep ◴[31 Mar 19 02:29 UTC] No.19533377[source]▶
I have a question, that was partially addressed in some comments in the last Timezone in Databases article earlier in the week -

What do people think about using Unix time in databases?

replies(4): >>19533555 #>>19533627 #>>19533651 #>>19535478 #
1. TheAceOfHearts ◴[31 Mar 19 03:31 UTC] No.19533627[source]▶
It depends on your use-case. You should also be aware of the Year 2038 problem [0].

What are you trying to achieve?

[0] https://en.wikipedia.org/wiki/Year_2038_problem

replies(1): >>19533747 #
2. robbiep ◴[31 Mar 19 04:01 UTC] No.19533747[source]▶
Largely timestamping. Accuracy to a high level (leap seconds) is irrelevant. Aware of 2038 problem, assume we will have some fun along the way to there along with the rest of the unix world!
replies(1): >>19534097 #
3. TheAceOfHearts ◴[31 Mar 19 05:38 UTC] No.19534097[source]▶
Storing Unix timestamps in a 64-bit signed integer field would probably be fine in that situation. The only downsides that come to mind are that you'd have to manually convert the number to an actual date when using it, and that you'd lose easy access to date-related functionality in the database.
replies(1): >>19535461 #
4. jeltz ◴[31 Mar 19 13:27 UTC] No.19535461{3}[source]▶
But also totally pointless since PostgreSQL's timestamptz type also uses 64 bits.