Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: TIMESTAMPTZ is incorrect #685

Closed
mjibson opened this issue Sep 27, 2016 · 7 comments · Fixed by #1597
Labels

Comments

@mjibson
Copy link
Member

@mjibson mjibson commented Sep 27, 2016

The docs for TIMESTAMPTZ say: "TIMESTAMPTZ stores a date and time pair with a time zone offset from UTC" however this isn't correct as both TIMESTAMP and TIMESTAMPTZ are stored in the same way: number of seconds and nanoseconds since the epoch. No timezone information is stored at all in either case.

In general the TZ type cares about the session offset and normal TIMESTAMP doesn't, but that's not always true. Ping me when you'd like to go over specifics of how these two types differ, as there are many edge cases here and pretty much only David knows how it all works.

@jseldess

This comment has been minimized.

Copy link
Contributor

@jseldess jseldess commented Sep 27, 2016

@mjibson, here's the related issue I mentione: cockroachdb/cockroach#9266

@jseldess jseldess added the bug label Oct 27, 2016
@jseldess jseldess changed the title TIMESTAMPTZ is incorrect sql: TIMESTAMPTZ is incorrect Oct 27, 2016
@mjibson

This comment has been minimized.

Copy link
Member Author

@mjibson mjibson commented Jun 15, 2017

A user just hit this issue. We need to correct the docs, because they are incorrect and misleading. https://forum.cockroachlabs.com/t/timezone-with-gorm/728

@jseldess

This comment has been minimized.

Copy link
Contributor

@jseldess jseldess commented Jun 15, 2017

@mjibson's response in forum:

Our docs for the WITH TIME ZONE type are incorrect (see #685). Both the WITH and WITHOUT types of TIMESTAMP are stored as UTC without any timezone info. The only difference is that the WITH TIME ZONE variety will send back data in whatever time zone your SQL session is using. (And I'm not sure if it defaults to UTC or the system's time zone.)

Our recommendation is to never ever use the WITH TIME ZONE type, but always use just TIMESTAMP, which does everything in UTC.

@jseldess jseldess added the O-external label Jun 15, 2017
@dt

This comment has been minimized.

Copy link
Member

@dt dt commented Jun 15, 2017

I'd change the first sentence from The TIMESTAMP data type stores a date and time pair in UTC, whereas TIMESTAMPTZ stores a date and time pair with a time zone offset from UTC. something like:

The TIMESTAMP data type stores a date and time in UTC.

The TIMESTAMP type has two variants: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. Both are always stored in UTC -- the difference between them is that WITH TIME ZONE uses the session offset, if set, when sending timestamps to clients, and when parsing received timestamps if they do not contain an explicit offset, while the WITHOUT TIME ZONE version ignores the session offset.

The WITHOUT TIME ZONE variant can sometimes lead to unexpected behaviors when it ignores a session offset, so it is generally recommended to use the WITH TIME ZONE variant. That said, session offsets can also be confusing or lead to bugs when switching between clients or frameworks, so localizing timestamps is often even better left to an application's presentation layer.

TIMESTAMP with no specifier is a shorthand for the WITHOUT TIME ZONE variant for legacy compatibility reasons, TIMESTAMPTZ is a shorthand for TIMESTAMP WITH TIME ZONE.

@mjibson

This comment has been minimized.

Copy link
Member Author

@mjibson mjibson commented Jun 15, 2017

I like the suggestion above, but I think it should also note as Ben brought up that some functions like extract will extract the day or whatever the parameter is in the time-zone converted timestamp, which happens before data is sent to the client.

@sploiselle

This comment has been minimized.

Copy link
Collaborator

@sploiselle sploiselle commented Jun 15, 2017

@mjibson Does that David's suggestion work for you?

@mjibson

This comment has been minimized.

Copy link
Member Author

@mjibson mjibson commented Jun 15, 2017

Yes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.