How to best store a timestamp in PostgreSQL?
39
votes
2
answers
70646
views
I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps.
### Assumptions
Users in different timezones will use the database for all CRUD functions.
I have looked at 2 options:
-
-
timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')
- bigint NOT NULL DEFAULT
For **timestamp
** I would send a string that would represent the exact (UTC) timestamp for the INSERT moment.
For **bigint
** I would store the exact same thing, but in a number format. (time zone issues are handled before millis is handed over to the server, so always millis in UTC.)
One main advantage with storing a bigint
could be that it would be easier to store and to retrieve, as passing a correctly formatted timestamp is more complex than a simple number (millis since Unix Epoc).
My question is which one would allow for the most flexible design and what could be the pitfalls of each approach.
Asked by Bam
(579 rep)
Jul 19, 2015, 08:08 PM
Last activity: Nov 21, 2024, 10:34 PM
Last activity: Nov 21, 2024, 10:34 PM