Redshift allows loading time-related types using its epoch representation as stated here by setting timeformat 'epochmillisecs' parameter of COPY command. It works great for TIMESTAMPs but something is broken for TIME columns.

Epoch value 1636984022000 (ms) that corresponds to 13:47:02.572000 is being imported as 22:41:11 by Redshift. I can see that it matches 13:47:02 UTC at PST (-08:00).

I tried alter user awsuser set timezone to 'UTC' and set timezone to default but it doesn't seem to help. What am I missing ?

CodePudding user response：

Sometimes timezone issues can be hard to diagnose because SQL clients perform a conversion on the field when displaying data.

A trick I use is to convert the timestamp to TEXT so that the SQL client does not alter its contents. So, try selecting the data as SELECT field::TEXT to verify how it is actually being stored.

This prevents the SQL client from performing any nicely-intentioned timezone conversion and lets you see the 'real' underlying data.