ERROR: function convert_timezone("unknown", "unknown", timestamp with time zone) does not exist

0

I have a column with datatype "timestamp with time zone" in UTC. I want to convert it to EST while accounting for daylight savings time. If we call the column [col1], I have tried the query as "convert_timezone('UTC','EST',col1) " and "convert_timezone('UTC','EDT',col1) ". When I run this, I receive this error message: "ERROR: function convert_timezone("unknown", "unknown", timestamp with time zone) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts. [ErrorId: 1-664f48e0-729d308e6412d4d328c6a041]". For context, I tried the "TIMEZONE" function as well, which runs without error but does not account for daylight savings time.

  • Along with this, in another column, i have to convert a VARCHAR datetime value in EST to a timestamp. Is there a way to convert this while maintaining EST as the time zone for the column, rather than setting the time zone as the default UTC

tobyo
asked a month ago133 views
1 Answer
0

To convert a "timestamp with time zone" column in UTC to EST while accounting for daylight savings time in Amazon Redshift.

You can also use the AT TIME ZONE function to convert the UTC timestamp to EST:

SELECT column_name AT TIME ZONE 'UTC' AT TIME ZONE 'EST' FROM your_table;

However you should be able to use CONVERT_TIMEZONE function to achieve the same result: I have done some basic testing and got the result below.

Enter image description here

AWS
JackieE
answered a month ago
  • Hi Jackie, neither of these seem to be working correctly. Due to daylight savings time, there should be a 4 hour difference between UTC and EST on April 12, rather than 5. Also, is there any reason you can see for why CONVERT_TIMEZONE would result in the error message I mentioned?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions