1

While converting UTC to CST in redshift database we have found out difference of 1 hour. We made use different workarounds but is not obtaining the desired result.

1) convert_timezone function not giving us the change we need.

Is there any approach that we can do at cluster level like spinning up a new cluster and restoring from snapshot. Can you please suggest any workarounds/solutions that you have made use of for this.

Cheers!!

narayanan s
  • 101
  • 2
  • 11

2 Answers2

2

I think, the difference of 1 hour must be because of Day-Light-Saving aka (DST) adjustments.

As far as I know Redshift supports both type of timezones with DST and without DST.

Make sure you use timezone name with DST in case you need DST time or without it if you need without DST impact.

For ex. CST is timezone for Central US without DST and CDT is timezone for Central US with DST.

With DST

select convert_timezone('CDT',  '2019-01-15 04:47:22');         convert_timezone
-------------
2019-01-14 23:47:22

Without DST

 select convert_timezone('CST',  '2019-01-15 04:47:22');         convert_timezone
 ------------
 2019-01-14 22:47:22

I hope this answers your question.

Red Boy
  • 5,429
  • 3
  • 28
  • 41
0

As documented in this other question, the best approach to handle daylight savings time automatically is to use the timezone names, instead of CST/CDT.

In your example, do something like

convert_timezone('UTC', 'US/Central', my_date_field)  -- GOOD

don't do this: convert_timezone('UTC', 'CST', my_date_field) -- BAD

https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html

Gabe
  • 5,113
  • 11
  • 55
  • 88