timestamp :: bigint ?

I have one question. Why PostgreSQL hasn’t implicit casting of timestamps as bigint? We all know that timestamps are stored as eight-byte integers. Yeah, I know that they can be stored as double precision floating-point, but this is deprecated option.

So on my server I successfully executed such thing:

CREATE CAST (timestamp AS bigint)

CREATE CAST (timestamptz AS bigint)

Any thoughts?


2 thoughts on “timestamp :: bigint ?

  1. Implicit casting can easily cause subtle bugs. One of the main benefits of a type system is so that you don’t do something like:

    ‘2009-01-01’ + 10

    accidentally. These errors can be much more subtle when both of those are variables rather than literals. And it can get even trickier with operator overloading, where it needs to figure out the operator to use based on the input types.

    ‘2009-02-01’::timestamp – ‘2009-01-01’

    Instead, you should do something like:

    ‘2009-01-01′ + ’10 microseconds’

    which is much safer.


  2. I think the short answer is that the fact that it might be an 8 byte integer under the hood is an implementation detail. Casting it to bigint would really be breaking the abstraction of the data type. So I at least would be quite opposed to such a cast being added.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s