I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps.
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
timestamp I would send a string that would represent the exact (UTC) timestamp for the INSERT moment.
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.