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')


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. Commented Jul 19, 2015 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
    Commented Jul 19, 2015 at 22:25

2 Answers 2


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');


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:

  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()

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

  • 1
    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
    Commented Jul 20, 2015 at 0:38

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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