17

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.

  • There are a lot of reasons why a timestamp is better than a bigint for representing timestamps. I can't think of of a single reason why a bigint would be better than a timestamp. – Lennart Jul 19 '15 at 21:07
  • The main reason I can think a BigInt can be easier is that it might be a lot easier to retrieve and store. I will update my questions. – Bam Jul 19 '15 at 22:25
22

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:

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:

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.

  • This made things clear. In addition to the fact that timestamps are stored as 8-byte integers, essential the same as storing as bigint, storing and retrieving with the "to_timestamp" functions makes this a much simpler choice. Thank you – Bam Jul 20 '15 at 0:38
7

You should always store data in it's native datatype so you can use the built-in functions. And the data type of a timestamp is obviously a timestamp.

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

  • My apologies, I meant to say that I would send a string for storage, not store a timestamp. Corrected it. – Bam Jul 19 '15 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.