0

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:

1792-03-29

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?

6
  • If you only want a date value (without any time), then define the column as date
    – user1822
    Commented Apr 23, 2022 at 12:56
  • @a_horse_with_no_name I don't know how to respond to this other than "it's not what I want". You have not understood my problem.
    – Homer
    Commented Apr 23, 2022 at 13:10
  • "I keep setting it (updating) to: 1792-03-29" - well, that is a date value.
    – user1822
    Commented Apr 23, 2022 at 13:12
  • @a_horse_with_no_name Yes, but that's not what typically is in there. Many are more specific.
    – Homer
    Commented Apr 23, 2022 at 13:13
  • The question is: in which time zone do you want to have the result? Commented Apr 25, 2022 at 7:52

1 Answer 1

0

'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.

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.