0

I recognize there are some similar questions, and I've read quite a few, so pardon me if I just missed the answer.

I have data that was stored as a TIMESTAMP data type, but now we want it to be stored as a TIMESTAMPTZ. I am running an ALTER statement that takes care of the conversion, however it seems to have an implied timezone that the data is stored in. Interestingly enough the data is stored in UTC time, via the ETL processes:

Sample data - current time as of run is 2023-09-27 13:51:09

-- +---+------------------------+
-- |id |start_date
-- +---+------------------------+
-- |0  |2023-09-27 19:51:09:953 |
-- |1  |2023-09-27 19:51:09:953 |

You can see I'm 6 hours behind UTC, which is fine and dandy. However, when I convert the column data type to a TIMESTAMPTZ (alter table test_timezone alter column start_date type timestamptz;) I get the following:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 19:51:09:953 -0600 |
-- |1  |2023-09-27 19:51:09:953 -0600 |

So it either doesn't recognize that it's UTC time, or thinks it's in my America/Denver timezone. I can get the correct timezone by running the following: alter table test_timezone alter column updated_at type timestamptz using updated_at at time zone 'UTC';:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 13:51:09:953 -0600 |
-- |1  |2023-09-27 13:51:09:953 -0600 |

However my desired results are to retain the UTC datetimes in the database, just display the correct timezone of the time stored:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 19:51:09:953 +0000 |
-- |1  |2023-09-27 19:51:09:953 +0000 |

(I'm not sure if that's how the actual UTC time is displayed, as I can't get it to display that way in my database, but the general idea of the desired results should be clear enough there.)

2
  • The point is that PostgreSQL doesn't store the time zone. When displayed as a string, the values are converted to the time zone specified by the value of the timezone parameter in your database session. Sep 28, 2023 at 5:59
  • That's helpful to understand, I thought it just knew, but makes sense. Sounds like it was a combination of both my IDE and Postgres and myself understanding the difference, but it's helpful to know that PostgreSQL doesn't actually store any timezone information.
    – codki
    Sep 28, 2023 at 17:15

1 Answer 1

1

I think I figured it out on my own. Unfortunately for me I use a software for SQL that was trying to be friendly by converting all timezones to my current timezone, after querying the data in a different software, the data was in fact in the format I wanted after I ran the following alter statement: alter table test_timezone alter column updated_at type timestamptz using updated_at at time zone 'UTC';

After that, it was just a manner of my IDE trying to be friendly and convert it for me in my timezone. Need to figure out how to change that, but I figured it out. Hopefully the alter statement helps someone else out!

1
  • "I use a software for SQL that was trying to be friendly by converting all timezones to my current timezone" Well, that is what timestamptz means. The PostgreSQL software itself will be doing that, although the client may in fact be further munging things.
    – jjanes
    Sep 28, 2023 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.