70

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
Moshe Katz
  • 15,992
  • 7
  • 69
  • 116
Subu Ganesh
  • 805
  • 1
  • 9
  • 11
  • 7
    Side note: your probably want to use `'America/New_York'` (from the [IANA time zone database](https://en.wikipedia.org/wiki/Tz_database)) as your time zone, not `'EST'`. The three letter abbreviations are imprecise (do you really mean UTC-0400, or do you want UTC-0300 during summer?) and often repeat across multiple countries, or even in one country (the time zones for both Alaska and Puerto Rico are often both labeled "AST") – Luis Casillas Nov 30 '17 at 23:49

8 Answers8

79

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;
BillRob
  • 4,659
  • 4
  • 26
  • 38
Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 1
    Thank you so much. But , this function is just for a custom time stamp value, right ? How do I do it if I need to change all the rows in a table, but not just a value ? – Subu Ganesh Mar 31 '16 at 19:53
  • As per your answer, this should give me the local time: select now() AT time zone 'IST'; But this does not. – Surya Oct 25 '19 at 02:30
  • 1
    This doesnt seem to account for daylight savings. When i use EST, it is still subtracting 5 hrs instead of 4, in April. – thentangler Apr 01 '22 at 02:06
  • 1
    Need full name time zone for daylight saving. Link https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES search for `PostgreSQL allows you to specify time zones in three different forms` and the following paragraph `In short, this is the difference ...` – Sany Liew Apr 19 '23 at 06:33
  • "This doesnt seem to account for daylight savings. When i use EST, it is still subtracting 5 hrs instead of 4, in April." This is because `'EST'` is "Eastern Standard Time" which always means UTC−05:00. By contrast, `'EDT'` is "Eastern Daylight Time", which always means UTC−04:00. If you want your system to automatically swap between EDT vs EST during the year according to a particular city's Daylight Savings calendar, use Sany Liew's recommendation of specifying the region: `'America/New_York'` – James Daily Aug 11 '23 at 16:14
  • If you ask for EST then of course the database should give you EST. I'm a bit surprised at other people's surprise. I suppose we don't have a "timezones are complicated" topic though – Richard Huxton Aug 13 '23 at 07:17
24

Similarly execute 

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

timezone
------------------------
 2015-10-24 21:38:46+00
(1 row)
Kevin Li
  • 2,068
  • 15
  • 27
  • Does this require your database to be set to assume UTC by default? – benjimin Feb 11 '18 at 03:18
  • benjimin Yes. Because when Postgres runs this command it will convert the timestamp value to EST but then display in your set time zone. Similar to the first part of @Richard Huxton's answer. They convert the timestamp to 'UTC' but it is displayed as +01. – adam-beck Feb 23 '18 at 18:29
  • 1
    As per your answer, this should give me the local time: select now() AT time zone 'IST'; But this does not. – Surya Oct 25 '19 at 02:30
9

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 ....
Leandro Castro
  • 518
  • 8
  • 14
7

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)
Yash Mochi
  • 769
  • 6
  • 15
1

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?

TheFlyingDutchMoose
  • 65
  • 5
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31601622) – fcce Apr 24 '22 at 08:17
1

Building off of @Leandro Castro's answer...

To get current time in in timezone, use the CURRENT_TIME function:

SELECT CURRENT_TIME(0) AT time zone 'utc' at time zone 'est';
DaveWoodall.com
  • 727
  • 6
  • 22
0

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

user33072
  • 1
0

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.

Elmira Behzad
  • 413
  • 4
  • 6