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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>
<mailto:david.g.johnston@xxxxxxxxx
<mailto:david.g.johnston@xxxxxxxxx>>> wrote:
On Wednesday, July 11, 2018, David Gauthier
<davegauthierpg@xxxxxxxxx <mailto:davegauthierpg@xxxxxxxxx>
<mailto:davegauthierpg@xxxxxxxxx
<mailto:davegauthierpg@xxxxxxxxx>>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
