1

Could someone please explain the logic that Postgres applies when dealing with the following situation.

A Django view receives a string representation of time in the following format:

18:30 PM

which it then persisted to Postgres. I created the 2 columns in the DB to highlight the difference in handling the above time:

start_at time NOT NULL,
start_at_tz timetz NOT NULL,

Here's what get saved in DB:

enter image description here

The behavior seems to be that some 7 hr difference that was applied to the timezone column. The value format doesn't seem to incorporate the 24 hr/day cutoff.

Earlier times get converted into:

13:00 PM

enter image description here

14:00 PM

enter image description here

Can someone please shed some light on the PostgreSQL bevahior. I'm on an EST time zone, if that' relevant.

Thank you in advance.

Improve this question
3
  • Like the documentation says, time with time zone is there because the standard says so, but it is broken by concept. Don't use it. Commented Aug 3, 2018 at 14:54
  • @LaurenzAlbe: would you mind elaborating a bit? Commented Aug 3, 2018 at 14:57
  • Done in an answer. Commented Aug 3, 2018 at 16:04

1 Answer 1

Reset to default
3

It seems that the database session that inserts the values is running with a different value of timezone than the session that you use to display the values.

time with time zone is interpreted in the session time zone for the current day and stored as time since midnight UTC.

You shouldn't use time with time zone, like the documentation says:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

A time zone only makes sense in combination with a date, because there are time zones where the offset changes during the year. If you are in such a time zone, the displayed values for time with time zone will vary through the year.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.