Convert a UTC timezone in postgresql to EST (local time)

Question

I am new to PostgreSQL and I was wondering if there is a direct way to just convert the timestamp values in a table to a different timezone using a function. In my case it is UTC to EST.

These are the values for example that I need to convert to EST (not just one value but all the values in the table)

date
-------------------
2015-10-24 16:38:46
2016-01-19 18:27:00
2016-01-24 16:14:34
2016-02-09 23:05:49
2016-02-11 20:46:26

Answer 1

Here in London, we are currently 1 hour ahead of UTC. So - if I take your timezone without timestamp and say it is in UTC I will get it printed for my local timezone.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC');
        timezone        
------------------------
 2015-10-24 17:38:46+01
(1 row)

But you want "EST" which seems to be somewhere in the Americas, judging by the value returned. You can wrap the expression in a little SQL function if you wanted to.

richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC') AT TIME ZONE 'EST';
      timezone       
---------------------
 2015-10-24 11:38:46
(1 row)

---

Edit: how to do it in a query

SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS local_timestamp
FROM my_table;

You will probably want to get an introductory book on SQL if this sort of thing is causing you problems.

Answer 2

Similarly execute

SELECT '2015-10-24 16:38:46'::timestamp AT time zone 'EST';

timezone
------------------------
 2015-10-24 21:38:46+00
(1 row)

Answer 3

I usually leave everything in UTC and convert when it is time to show. I use something like:

SELECT my_date_utc AT time zone 'utc' at time zone 'est' From ....

Answer 4

If you have problem accessing with your zone, you can simply pass your zone interval also. To convert timestamp from IST to UTC.

SELECT '2020-12-14 06:38:46'::timestamp AT time zone INTERVAL '+05:30';

timezone
------------------------
2015-10-24 11:38:46+00
(1 row)

To convert timestamp from UTC to IST.

SELECT '2020-12-14 06:38:46'::timestamp AT time zone INTERVAL '-05:30';

timezone
------------------------
2020-12-14 12:08:46+00
(1 row)

Answer 5

You should always store the main reference of a date in UTC and either convert it to a time zone in your queries or store the specific timezone version of the data in another column. The reason for this is that it is quick and easy to convert a date from UTC to another time zone as long as you know that the timezone that it is stored as is UTC. It takes the guess work out of it. Alternatively, you can store the date WITH the timezone.

If you have an operation that automatically populates the date with the system clock of your server, then you can either A: Change the operation to use UTC time B: Change the system clock on the server to UTC

Answer 6

I had the same problem, I am working with different regions and timezones, I need to just fix the timezone in the query the way it doesn't effect other customers around the regions and I havent changed the table structure or any thing(Open–closed principle) . What I did In my query: SELECT TO_CHAR(current_timestamp at time zone 'Australia/Melbourne', 'DD/MM/YYYY hh24:mi AM') as date_of_extract This worked for me and I could change the 'UTC' defult timezone for my postgressql to the 'Australia/Melbourne'(any time zone you are looking into). hope this is helpful.

Answer 7

It is 12:22 here in Los Angeles now. I find that I have to reverse the UST and america/los_angeles arguments:

ods=> SELECT NOW(),(NOW() AT TIME ZONE 'america/los_angeles') AT TIME ZONE 'utc';;
              now              |           timezone
-------------------------------+-------------------------------
 2022-04-22 19:22:35.943605+00 | 2022-04-22 12:22:35.943605+00
(1 row)

Am I missing something?