CODEWITHSUNDEEP
sqltype-conversionamazon-redshift
Pankaj Kumar
Pankaj Kumar

Reputation: 85

Redshift CONVERT_TIMEZONE Issue

I am facing an issue with the time conversion.

Whenever I tried to change the time to timestamp. It does not work. I have tried many of functions from http://docs.amazonaws.cn/en_us/redshift/latest/dg/Date_functions_header.html

But still a single function did not convert the date time to timestamp. An eg. I need date time (2017-12-11 23:38:11) as timestamp (1513036800) format.

Can you please guys tell me the exact function to convert that?

Also I have tried to use CONVERT_TIMEZONE function. It works fine, whenever I passed the static date inside this like: CONVERT_TIMEZONE('GMT','GMT -1','2017-12-11 23:38:11')

But if I change the date with my variable name, I am getting an error message:

"ERROR: function convert_timezone("unknown", "unknown", character varying) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts."

Upvotes: 1

Views: 11810

Answers (1)

AlexYes
AlexYes

Reputation: 4208

Per error message it looks like your timestamp is stored in a varchar column. You have to change the column to timestamp or convert the value on the fly to be able to use timestamp functions. I suggest the first because it has better performance. Redshift doesn't support column data type alteration, so to do this you have to recreate the table DDL with timestamp data type for time columns, insert data to the new table and replace the old table with it.

As for dynamic conversion, it's like this:

EXTRACT('epoch' FROM CONVERT_TIMEZONE('GMT','GMT -1',your_column::timestamp))

you just don't need to use ::timestamp part if you convert the column

Upvotes: 2

Related Questions