How To Convert A Timestamp Field To Int8? Or Just Drop The Column And Make A New One?

I have a PostgreSQL table with a column of type timestamp. I had included this column a while ago just in case I wanted to use it for something in the future. I am now looking to c

Solution 1:

First of all, the objective is undefined without clearing up what that int8 is going to represent. Seconds since the epoch? Milliseconds? Microseconds? (Won't matter in your particular case with all NULL values, but the next reader might be misguided.)

Next, in Postgres there is no cast defined for timestamp --> bigint (basically for the same reason). You need a valid expression for the USING clause.

Assuming you want microseconds because that's preserving the original microsecond resolution of Postgres timestamps, this will do the job:

ALTERTABLE public.new_ambient_data
   ALTERCOLUMN sensor_date TYPE int8 USING (extract(epoch FROM sensor_date)*1000000)::int8;

Notably, the Postgres epoch for timestamps starts at 2000-01-01 00:00:00 UTC, unlike the UNIX epoch starting at 1970-01-01 00:00:00 UTC. But extract() returns the UNIX epoch (which can be converted back to timestamptz with to_timestamp()). So just converting the internal value wouldn't do.

For your particular case (all values NULL), it's simpler to use text as stepping stone. Every type can be cast from and to text (as long as the value is compatible).

ALTERTABLE public.new_ambient_data
   ALTERCOLUMN sensor_date TYPE int8 USING sensor_date::text::int8;

And yes, it's probably cheaper to convert the column in place, than to drop and recreate it. While the column is all NULL, the operation is very cheap either way, as there is no actual tuple data, only a bit in the NULL bitmap. Neither way will trigger a table rewrite.

A newly added column always goes to the end of the columns list, while the converted one stays in place. Depends on what you want.

Finally, don't do it at all. The data type timestamp (or timestamptz) is typically superior to storing temporal information as generic bigint in multiple ways. See details in Laurenz' answer!

See:

• Ignoring time zones altogether in Rails and PostgreSQL
• How to get the date and time from timestamp in PostgreSQL select query?
• How to round off milliseconds value from timestamp(0) in PostgreSQL?

Solution 2:

First, I want to dissuade you from doing that. It is much better to use a timestamp column than to use a numeric column:

• the values can more easily be understood by a human

• you can make use of date arithmetic, so you don't lose anything:

  timestamp - timestamp → interval

  timestamp + interval → timestamp

  interval / double precision → interval

  This makes your queries more readable

• if you use timestamp with time zone, you can have PostgreSQL handle time zone conversions for you

• useful functions like date_part and date_trunc (and date_bin from v14 on!) to truncate the values and extract individual components

Internally, a timestamp is stored as a number anyway, so you don't lose performance.

The actual conversion is given in Erwin's answer, so I won't repeat that here.