Inserting current_timestamp at time zone GMT gives local time instead

Question

I got a PostgreSQL database in which I have a date field in the invoices table:

Column           |           Type           |
payment_date     | timestamp with time zone |

The server is located at GMT-5, as you can see:

$date
Tue Jan 22 17:33:01 EST 2019

Getting the GMT time via a PostgreSQL command gives me:

select current_timestamp at time zone 'GMT';
          timezone
----------------------------
 2019-01-22 22:33:01.087354

The problem is when I do an insertion/update:

update invoices set payment_date = current_timestamp at time zone 'GMT'

Then, when I get the query result...

select payment_date from invoices

it gives me:

2019-01-22 22:33:01.087354-05

Wrong! should be giving me 2019-01-22 22:33:01.087354-00

What am I doing wrong?

score 2 · Accepted Answer · answered Jan 22 '19 at 22:57

Your field payment_date is of the (correct) type timestamp with time zone, so the timestamp without time zone that is the result of the AT TIME ZONE operation is converted using your session time zone.

PostgreSQL's timestamp with time zone data type does not store time zone information, it stores an UTC timestamp that is converted to the session time zone on display.

You are best advised to store the timestamp as timestamp with time zone and convert it to the desired time zone when you display it.

Inserting current_timestamp at time zone GMT gives local time instead

1 Answers1