Convert text to timestamp in redshift

amazon-redshift to-date to-timestamp
user5157294 picture user5157294 · Jul 26, 2015 · Viewed 33.9k times · Source

I have a text field "presence_changed_at" with text values i.e. '2014/12/17 08:05:28 +0000. I need to convert this into timestamp. In postgreSQL there is function TO_TIMESTAMP(), however in redshift this does not seem to be supported. I can get the date without time by

TO_DATE("presence_changed_at",'YYYY/MM/DD HH24:MI:SS')

which produces

2014-12-12

but i can't find any way to get TIMESTAMP format.

Thanks in advance for solving this

Answer

Harry Moreno picture Harry Moreno · Nov 17, 2017 
cast(column as timestamp)

worked for me on redshift. See http://devdocs.io/postgresql~9.4/sql-expressions#SQL-SYNTAX-TYPE-CASTS

Related questions

Show tables, describe tables equivalent in redshift

I'm new to aws, can anyone tell me what are redshifts' equivalents to mysql commands? show tables -- redshift command describe table_name -- redshift command

Read More
Alter column data type in Amazon Redshift

How to alter column data type in Amazon Redshift database? I am not able to alter the column data type in Redshift; is there any way to modify the data type in Amazon Redshift?

Read More
In Redshift/Postgres, how to count rows that meet a condition?

I'm trying to write a query that count only the rows that meet a condition. For example, in MySQL I would write it like this: SELECT COUNT(IF(grade < 70), 1, NULL) FROM grades ORDER BY id DESC; However, when I …

Read More