1

Currently, I have .csv data uploaded to my Redshift cluster in the following format (sample):

period_start            glance_view_count
2016/01/01 03:00 PST    0
2016/01/01 04:00 PST    800
2016/01/01 05:00 PST    100
2016/02/09 12:00 PST    99
2016/02/09 13:00 PST    18

period_start is currently text data type.

I would like to insert this data into a separate table where the period_start column is formatted timestamp or timestamptz. Is there a TO_DATE() function or similar I can call to convert text to the desired format?

Ray
  • 3,137
  • 8
  • 32
  • 59
  • 1
    If everything else fails, read the manual: http://docs.aws.amazon.com/redshift/latest/dg/r_TO_TIMESTAMP.html –  Feb 15 '17 at 20:45
  • @a_horse_with_no_name Thanks for this! `to_timestamp(period_start, 'yyyy/mm/dd HH:MI') as period_start` seems to do the trick. – Ray Feb 15 '17 at 21:07
  • Possible duplicate of [Convert text to timestamp in redshift](http://stackoverflow.com/questions/31636304/convert-text-to-timestamp-in-redshift) – Ray Feb 15 '17 at 21:47

1 Answers1

1

Thanks to another user, per this: http://docs.aws.amazon.com/redshift/latest/dg/r_TO_TIMESTAMP.html

SELECT to_timestamp(ios.period_start, 'yyyy/mm/dd HH:MI') as period_start does the trick!

Ray
  • 3,137
  • 8
  • 32
  • 59