Re: timestamp (military) at time zone without the suffix - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: timestamp (military) at time zone without the suffix |
Date | |
Msg-id | 652f88fe-af0f-4f9c-0ef1-ee814b8a05ac@aklaver.com Whole thread Raw |
In response to | timestamp (military) at time zone without the suffix (David Gauthier <davegauthierpg@gmail.com>) |
List | pgsql-general |
On 07/11/2018 02:21 PM, David Gauthier wrote: Pleas reply to list also. Ccing list. > Table columns have already been defined with timestamp datatype. The > on;y way I know of to fix this is to... > > 1) add a new column as timestamptz called 'tmp' (whatever) > 2) update tmp with the value in the timestamp collumn perhaps using "at > time zone 'utc' " > 3) drop the original timestamp column > 4) recreate the column with the same name but with data type timestamptz > 5) Move all the records over to this column (from tmp) > 6) drop the tmp column. > > There would be some needed downtime to do this of course. > > Is there an easier way? create table ts_test(id int, ts_fld timestamp); insert into ts_test values (1, now()), (2, now() - interval '1 day'); test_(aklaver)> select * from ts_test ; id | ts_fld ----+---------------------------- 1 | 2018-07-11 14:24:43.960989 2 | 2018-07-10 14:24:43.960989 (2 rows) Assuming the timestamp values where at UTC: alter table ts_test alter COLUMN ts_fld type timestamptz using ts_fld at time zone 'UTC'; test_(aklaver)> select * from ts_test ; id | ts_fld ----+------------------------------- 1 | 2018-07-11 07:28:17.279899-07 2 | 2018-07-10 07:28:17.279899-07 The above depends on you knowing what the timestamps in the timestamp field where entered as. I would test first. See below for more info: https://www.postgresql.org/docs/10/static/sql-altertable.html > > On Wed, Jul 11, 2018 at 5:14 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 07/11/2018 01:34 PM, David Gauthier wrote: > > Thanks Everyone, they all work, but TL's seems to be the simplest... > select current_timestamp(0) at time zone 'utc' > > I'm kinda stuck with the timestamp data type (vs timestamptz). > Wondering if I can stick with that. > > > The above is at little unclear. Can you change the data type or not? > If you can your life will be a lot easier if you change it to > timestamptz. > > > One last question... > I want to store the current UTC date/time in the DB. Does PG > unconditionally store something like UTC, then let the queries > figure out how they want to look at it (with "at time zone" and > "to_char()" etc...) ? Or do I have to intentionally store the > UTC value somehow? > > > Per: > > https://www.postgresql.org/docs/10/static/datatype-datetime.html > <https://www.postgresql.org/docs/10/static/datatype-datetime.html> > > "For timestamp with time zone, the internally stored value is always > in UTC (Universal Coordinated Time, traditionally known as Greenwich > Mean Time, GMT). An input value that has an explicit time zone > specified is converted to UTC using the appropriate offset for that > time zone. If no time zone is stated in the input string, then it is > assumed to be in the time zone indicated by the system's TimeZone > parameter, and is converted to UTC using the offset for the timezone > zone. > > When a timestamp with time zone value is output, it is always > converted from UTC to the current timezone zone, and displayed as > local time in that zone. To see the time in another time zone, > either change timezone or use the AT TIME ZONE construct (see > Section 9.9.3). > > Conversions between timestamp without time zone and timestamp with > time zone normally assume that the timestamp without time zone value > should be taken or given as timezone local time. A different time > zone can be specified for the conversion using AT TIME ZONE." > > > Right now the code is just inserting and updating records using > "localtimestamp(0)". > > > > > On Wed, Jul 11, 2018 at 4:11 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> > <mailto:david.g.johnston@gmail.com > <mailto:david.g.johnston@gmail.com>>> wrote: > > On Wednesday, July 11, 2018, David Gauthier > <davegauthierpg@gmail.com <mailto:davegauthierpg@gmail.com> > <mailto:davegauthierpg@gmail.com > <mailto:davegauthierpg@gmail.com>>> wrote: > > OK, the "to_char" gets rid of the timezone extension. > But the > times still don't make sense. > > When I go to store this in a DB, I want to store the > UTC time. How d I do that ? > > Use the data type that represents exactly that, > timestamptz. Using > the timestamp data type is generally not what you want even > if you > can get the manipulation logic figured out. > > David J. > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: