You can login if you already have an account or register by clicking the button below.
Registering is free and all you need is a username and password. We never ask you for your e-mail.
[–]logos_ethos0 points
1 point
1 point
(+1|-0)
ago
(edited ago)
I forgot a file, so I closed the other request and made request 909 (I edited the comment to point to the latest one).
Correct, it is not needed. The now() function is time zone aware. The reason why you needed the "at time zone 'utc'" is because you were using a data type that has no knowledge of time zones when stored, so you had to convert it to a consistent timezoneless value after converting it to UTC. The timestamptz stores in UTC on disk and transparently converts it to UTC from the now() function. When you query it, it is transparently presented in the time zone in your TimeZone GUC. I set this GUC at the database level so that you do not have to set it for the user, session, or server configuration, although you could do it those ways. This will make SQL work easier in the future. Think of timestamptz as the data type to use for referencing actual moments of time, and the timestamp data type for referencing fictional times. An example of a fictional time is midnight in whatever time zone anyone is governed by in their legal contract, and you have no knowledge of what timezone they might actually be in. So just midnight is stored, and it is up to the user to know what that is. tz is better if you actually know the timezone.
Here is another person's thoughts about it: http://justatheory.com/computers/databases/postgresql/use-timestamptz.html The partitioning concern mentioned there is only applicable if you partition with non-UTC boundaries between the partitions. If you are using partition boundaries in UTC (which you would want to do if you used partitions), then there is no problem there.
Note that when "alter database {dbName} set TimeZone" is run, any existing database connection will retain the prior TimeZone value. So if you have connections that connect to the empty database, they will have to be closed and reopened after the .sql files are run.
view the rest of the comments →
[–] PuttItOut 0 points 2 points 2 points (+2|-0) ago
Thank you so much. I will try to pull this down and run unit tests today or this weekend.
Question:
now() at time zone 'utc'is not needed? Is utc the default. We do store everything in utc.[–] logos_ethos 0 points 1 point 1 point (+1|-0) ago (edited ago)
I forgot a file, so I closed the other request and made request 909 (I edited the comment to point to the latest one).
Correct, it is not needed. The now() function is time zone aware. The reason why you needed the "at time zone 'utc'" is because you were using a data type that has no knowledge of time zones when stored, so you had to convert it to a consistent timezoneless value after converting it to UTC. The timestamptz stores in UTC on disk and transparently converts it to UTC from the now() function. When you query it, it is transparently presented in the time zone in your TimeZone GUC. I set this GUC at the database level so that you do not have to set it for the user, session, or server configuration, although you could do it those ways. This will make SQL work easier in the future. Think of timestamptz as the data type to use for referencing actual moments of time, and the timestamp data type for referencing fictional times. An example of a fictional time is midnight in whatever time zone anyone is governed by in their legal contract, and you have no knowledge of what timezone they might actually be in. So just midnight is stored, and it is up to the user to know what that is. tz is better if you actually know the timezone.
Here is another person's thoughts about it: http://justatheory.com/computers/databases/postgresql/use-timestamptz.html The partitioning concern mentioned there is only applicable if you partition with non-UTC boundaries between the partitions. If you are using partition boundaries in UTC (which you would want to do if you used partitions), then there is no problem there.
Note that when "alter database {dbName} set TimeZone" is run, any existing database connection will retain the prior TimeZone value. So if you have connections that connect to the empty database, they will have to be closed and reopened after the .sql files are run.