Re: timestamp (military) at time zone without the suffix

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