How to best store a timestamp in PostgreSQL?

Question

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.

Answer 1

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around:

• Time zone storage in PostgreSQL timestamps
• Ignoring timezones altogether in Rails and PostgreSQL

To address your concern:

passing a correctly formatted timestamp is more complex than a simple number

You can pass and retrieve a UNIX epoch either way if you prefer:

SELECT to_timestamp(1437346800)
     , extract(epoch FROM timestamptz '2015-07-20 01:00+02');

Related:

• Truncate timestamp to arbitrary intervals
• Aggregating (x,y) coordinate point clouds in PostgreSQL

If you want to store the current timestamp with writes to the DB, use a timestamptz column with default value now(). The system time on the DB server is typically much more reliable and consistent than multiple clients handing in their respective notion of what time it is.
For INSERT it can be as simple as:

CREATE TABLE foo (
  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()
);

And just don't write to that column. It's filled in automatically.

Answer 2

You should always store data in its native data type so you can use the built-in functions. The data type of a timestamp is: timestamp.

As an aside, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation.