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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>
> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>> wrote:
>
>     On Wednesday, July 11, 2018, David Gauthier
>     <davegauthierpg(at)gmail(dot)com <mailto:davegauthierpg(at)gmail(dot)com>
> <mailto:davegauthierpg(at)gmail(dot)com
> <mailto:davegauthierpg(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com