It might be possible to change this in your application's settings. To create a new table, you use the CREATE TABLE statement.
#POSTGRESQL TIMESTAMP CREATE TABLE DRIVER#
Please note, if you always see the same timestamp representation, it might be that your database application or driver applies the current session default time zone instead. For example, use SELECT current_setting('timezone'). It is possible to refer to the time zone of your settings in queries. The latter has the advantage, that it supports variations over time in a region for legal, commercial, and social purposes, like daylight savings or offset changes. This can be either done as UTC offset or with time zone names. Therefore, we must store time zone information in a separate column, if we don't want to loose this information. The timestamp with time zone type, just adjusts to UTC during insertion. In this blog post, we have seen that Postgres never stores time zone information, but always a timestamp in UTC. Our table might look like this:ġ select 2 id, 3 value, 4 measured_at as measured_at_local, 5 measured_at at time zone measured_time_zone as measured_at_original, 6 measured_time_zone
Solution #2 - Storing time zone namesĪs discussed before, we need to store the time zone name as text in a separate column. A better solution, therefore, is to always use a name of a time zone. Such a region might decide to switch to another time zone without notification or to use or not use the summer time adjustment.
Time zones are subject to change over time in a region for legal, commercial, and social purposes. They might not correspond to time zones either at some point in the past or future. We build it as timestamp with time zone, because we do not want to calculate the offset inside our application logic, but rather rely on Postgres methods.Ī problem with this solution is, that UTC offsets are not safe to use over time. So, measured_at holds the date and time stored in UTC as all timestamps in Postgres. 1 create table sensors ( 2 id int primary key, 3 value double precision, 4 measured_at timestamp with time zone, 5 measured_utc_offset int 6 ) 7 8 insert into sensors ( id, value, measured_at, measured_utc_offset ) 9 values 10 ( 0, 33.7, ' 03:00:00+00' :: timestamp with time zone, 0 ), - UTC 11 ( 1, - 1.3, ' 03:00:00+02' :: timestamp with time zone, 2 ), - Europe/Rome 12 ( 2, 12.0, ' 03:00:00-04' :: timestamp with time zone, - 4 ) - America/New_York 13
0 kommentar(er)
