Sample Header Ad - 728x90

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