Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cast timestamp to integer in Redshift

I'm trying to cast or convert a timestamp column to an integer value in a Redshift database. I know it is possible in other SQL-based databases and I've done this before, however in Redshift I can't get it to work.

I've tried some variations of cast and also convert (http://docs.aws.amazon.com/redshift/latest/dg/r_CAST_function.html).

Here is an example:

SELECT cast(finish_time as integer) FROM table;

It gives me the error message:

SQL Error Invalid operation: cannot cast type timestamp without time zone to integer;

Is it possible to get a timestamp as an integer?

like image 752
J. Schneider Avatar asked Jul 31 '17 14:07

J. Schneider


1 Answers

try select extract('epoch' from finish_time), it will give you Unix ms timestamp

like image 141
AlexYes Avatar answered Oct 13 '22 21:10

AlexYes