timestamp (military) at time zone without the suffix

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

timestamp (military) at time zone without the suffix

David Gauthier
Hi:

I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix.  

Below commands were run nearly at the same time...

sqfdev=> select now()::timestamp(0)  ;
         now
---------------------
 2018-07-11 15:27:12
(1 row)

...then immediately...

sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
        timezone
------------------------
 2018-07-11 11:27:12-04
(1 row)


15:27:12 makes sense (it's a bout 3:30 in the afternoon EST).
11:27:12 doesn't make sense.  UTC is 5 hours ahead.  I would have expected either 20:27 (if it stuck to military time, which I want), or 08:27 (P.M., non-military time)

And I want to get rid of the -04 suffix.  

Is there a way to do this ?

Thanks !
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> wrote:
Hi:

I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix.  

Below commands were run nearly at the same time...

sqfdev=> select now()::timestamp(0)  ;
         now
---------------------
 2018-07-11 15:27:12
(1 row)

...then immediately...

sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
        timezone
------------------------
 2018-07-11 11:27:12-04
(1 row)


15:27:12 makes sense (it's a bout 3:30 in the afternoon EST).
11:27:12 doesn't make sense.  UTC is 5 hours ahead.
 
Apparently it's only four hours ahead of your server's time zone setting. 

 
  I would have expected either 20:27 (if it stuck to military time, which I want), or 08:27 (P.M., non-military time)

And I want to get rid of the -04 suffix.  

Is there a way to do this ?

Specify an appropriate format string with the to_char function.


David J.

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by David Gauthier
On 07/11/2018 12:36 PM, David Gauthier wrote:

> Hi:
>
> I would like to get the utc timestamp, 24-hr clock (military time),
> without the time zone suffix.
>
> Below commands were run nearly at the same time...
>
> sqfdev=> select now()::timestamp(0)  ;
>           now
> ---------------------
>   2018-07-11 15:27:12
> (1 row)
>
> ...then immediately...
>
> sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
>          timezone
> ------------------------
>   2018-07-11 11:27:12-04
> (1 row)
>
>
> 15:27:12 makes sense (it's a bout 3:30 in the afternoon EST).
> 11:27:12 doesn't make sense.  UTC is 5 hours ahead.  I would have
> expected either 20:27 (if it stuck to military time, which I want), or
> 08:27 (P.M., non-military time)
>
> And I want to get rid of the -04 suffix.
>
> Is there a way to do this ?

test=> show TimeZone;
   TimeZone
------------
  US/Pacific
(1 row)

test=> select now();
               now
-------------------------------
 

  2018-07-11 12:44:57.757347-07
 

(1 row)
 

 
 

test=> select (now() at time zone 'UTC')::timestamp(0);
       timezone
 

---------------------
 

  2018-07-11 19:45:00
 

(1 row)



>
> Thanks !


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by David Gauthier
On 07/11/2018 12:36 PM, David Gauthier wrote:
> select now()::timestamp(0) at time zone 'utc' ;
Or:

test=> select now();
               now
-------------------------------
  2018-07-11 12:51:50.498416-07
(1 row)

test=>  select now()::timestamptz(0) at time zone 'utc' ;
       timezone
---------------------
  2018-07-11 19:51:52
(1 row)


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by David Gauthier
David Gauthier <[hidden email]> writes:
> sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
>         timezone
> ------------------------
>  2018-07-11 11:27:12-04
> (1 row)

You're doing it wrong: coercing to timestamp already involves a rotation
to local time, and then "at time zone" says to interpret that as a time
in UTC, and the output is a timestamptz which will be displayed in your
local time.

I think the result you want is more like

        select (now() at time zone 'utc')::timestamp(0);

although personally I'd choose some other way of dropping the fractional
second, probably

        select current_timestamp(0) at time zone 'utc';

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by David Gauthier
On 07/11/2018 12:36 PM, David Gauthier wrote:
> Hi:
>
>
>
> And I want to get rid of the -04 suffix.
>
> Is there a way to do this ?

For the details see:

https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

>
> Thanks !


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
In reply to this post by David G Johnston
OK, the "to_char" gets rid of the timezone extension.  But the times still don't make sense.  

UTC should be 5 hours ahead, not behind.  It should be EST plus 5 hours (or 4 for DST), not minus.  That's why I said I expected 20:27 .  


When I go to store this in a DB, I want to store the UTC time.  How d I do that ? 

insert into foo (dt) values (localtimestamp(0) at time zone 'utc') ???   


On Wed, Jul 11, 2018 at 3:45 PM, David G. Johnston <[hidden email]> wrote:
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> wrote:
Hi:

I would like to get the utc timestamp, 24-hr clock (military time), without the time zone suffix.  

Below commands were run nearly at the same time...

sqfdev=> select now()::timestamp(0)  ;
         now
---------------------
 2018-07-11 15:27:12
(1 row)

...then immediately...

sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
        timezone
------------------------
 2018-07-11 11:27:12-04
(1 row)


15:27:12 makes sense (it's a bout 3:30 in the afternoon EST).
11:27:12 doesn't make sense.  UTC is 5 hours ahead.
 
Apparently it's only four hours ahead of your server's time zone setting. 

 
  I would have expected either 20:27 (if it stuck to military time, which I want), or 08:27 (P.M., non-military time)

And I want to get rid of the -04 suffix.  

Is there a way to do this ?

Specify an appropriate format string with the to_char function.


David J.


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
On 07/11/2018 12:59 PM, David Gauthier wrote:

> OK, the "to_char" gets rid of the timezone extension.  But the times
> still don't make sense.
>
> UTC should be 5 hours ahead, not behind.  It should be EST plus 5 hours
> (or 4 for DST), not minus.  That's why I said I expected 20:27 .
>
>
> When I go to store this in a DB, I want to store the UTC time.  How d I
> do that ?
>
> insert into foo (dt) values (localtimestamp(0) at time zone 'utc') ???

What is the data type for foo.dt?

>
>
> On Wed, Jul 11, 2018 at 3:45 PM, David G. Johnston
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On Wednesday, July 11, 2018, David Gauthier
>     <[hidden email] <mailto:[hidden email]>> wrote:
>
>         Hi:
>
>         I would like to get the utc timestamp, 24-hr clock (military
>         time), without the time zone suffix.
>
>         Below commands were run nearly at the same time...
>
>         sqfdev=> select now()::timestamp(0)  ;
>                   now
>         ---------------------
>           2018-07-11 15:27:12
>         (1 row)
>
>         ...then immediately...
>
>         sqfdev=> select now()::timestamp(0) at time zone 'utc' ;
>                  timezone
>         ------------------------
>           2018-07-11 11:27:12-04
>         (1 row)
>
>
>         15:27:12 makes sense (it's a bout 3:30 in the afternoon EST).
>         11:27:12 doesn't make sense.  UTC is 5 hours ahead.
>
>     Apparently it's only four hours ahead of your server's time zone
>     setting.
>
>            I would have expected either 20:27 (if it stuck to military
>         time, which I want), or 08:27 (P.M., non-military time)
>
>         And I want to get rid of the -04 suffix.
>
>         Is there a way to do this ?
>
>
>     Specify an appropriate format string with the to_char function.
>
>     https://www.postgresql.org/docs/10/static/functions-formatting.html
>     <https://www.postgresql.org/docs/10/static/functions-formatting.html>
>
>     David J.
>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
In reply to this post by David Gauthier
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> 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. 
Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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.

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?

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 <[hidden email]> wrote:
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> 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. 

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> wrote:
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?

If you use timestamptz the server interprets the stored value as being UTC.  If you use timestamp the server interprets the stored value using whatever the current Time Zone happens to be when the value is read (or it uses the at time zone value if specified).

David J.
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by David Gauthier
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

"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
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On Wednesday, July 11, 2018, David Gauthier
>     <[hidden email] <mailto:[hidden email]>> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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
>         <[hidden email] <mailto:[hidden email]>
>         <mailto:[hidden email]
>         <mailto:[hidden email]>>> wrote:
>
>              On Wednesday, July 11, 2018, David Gauthier
>              <[hidden email] <mailto:[hidden email]>
>         <mailto:[hidden email]
>         <mailto:[hidden email]>>> 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
>     [hidden email] <mailto:[hidden email]>
>
>


--
Adrian Klaver
[hidden email]