Why does PostgreSQL keep resetting my date to the wrong “timezone”, with “seconds”, as I try to remove it?

Posted on

Question :

I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an

I have this timestamp with timezone column value in PG:

1792-03-29 00:00:00+01:12:12

I keep setting it (updating) to:


But PostgreSQL just reverts back to how it was from the beginning.

The issue with that is that PHP’s strtotime gets confused if it sees seconds in the timezone part.

PHP understands:

1792-03-29 00:00:00+01:12

But not:

1792-03-29 00:00:00+01:12:12

And no matter what I do, PostgreSQL keeps reverting to the format with the seconds. Very frustrating. I have also tried to set it to:

1792-03-29 00:00:00+01:12

But even then, it just adds those seconds back to the timezone part.

What am I supposed to do to solve this?

Answer :

‘1792-03-29 00:00:00+01:12:12’ is not what is in PG. That is the output format that PG dynamically produces in response to your ‘timezone’ configuration setting being applied to a timestamptz value. What is stored in the database is just some microseconds difference from a UTC-like reference. If your timezone setting doesn’t change and the microsecond difference value stored doesn’t change and PG internal table of timezone rules doesn’t change, the dynamic output won’t change either.

You could always use text processing functions (either in PG or in PHP) to strip off the timezone if you don’t want it. Or fix this “feature” in PHP strtotime function. Or just store things as text if you want the database to mindlessly regurgitate to you the same text you put in, without any comprehension of what the text might mean.

Leave a Reply

Your email address will not be published. Required fields are marked *