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

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
> <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>> 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