Thread: to_timestamp alternatives

Hi. I have a practical need to convert some badly-formatted date/times into
'timestamp without time zone' data types.  Like other scientists, I try to
avoid timezone problems by sticking to UTC and using the 'timestamp without
time zone' data type whenever possible.

In this case, I used the to_timestamp() function as follows:
SELECT to_timestamp('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
HH24:MI:SS')

...but I discovered that the command above gives me the same result one hour
earlier:
SELECT to_timestamp('09.03.2014'||' '||lpad('2:00:00',8,'0'),'DD.MM.YYYY
HH24:MI:SS').

That's because to_timestamp was silently converting into my local time zone
(UTC -7), even though I was putting the result into a 'timestamp without
time zone' variable.  Like commenters on the thread "to_timestamp() and
timestamp without time zone", I consider the silent conversion to be bad
behavior, or at least I wish that the documentation warned the user more
clearly, and I would greatly prefer a function that just dealt in UTC.

In the recent thread "BUG #12739: to_timestamp function conver string to
time incorrectly", tom lane suggests avoiding to_timestamp().  However, I
don't see an easy way to get around it in my case.  Can anyone suggest a
good alternative?  Please note that I want to avoid relying on global
variables such as 'SET TIMEZONE = ...' if possible, since those just
introduce more potential for confusion, IMHO.

Thanks!



--
View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 12/31/2015 12:30 PM, gkhan wrote:
> Hi. I have a practical need to convert some badly-formatted date/times into
> 'timestamp without time zone' data types.  Like other scientists, I try to
> avoid timezone problems by sticking to UTC and using the 'timestamp without
> time zone' data type whenever possible.
>
> In this case, I used the to_timestamp() function as follows:
> SELECT to_timestamp('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')
>
> ...but I discovered that the command above gives me the same result one hour
> earlier:
> SELECT to_timestamp('09.03.2014'||' '||lpad('2:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS').
>
> That's because to_timestamp was silently converting into my local time zone
> (UTC -7), even though I was putting the result into a 'timestamp without
> time zone' variable.  Like commenters on the thread "to_timestamp() and
> timestamp without time zone", I consider the silent conversion to be bad
> behavior, or at least I wish that the documentation warned the user more
> clearly, and I would greatly prefer a function that just dealt in UTC.
>
> In the recent thread "BUG #12739: to_timestamp function conver string to
> time incorrectly", tom lane suggests avoiding to_timestamp().  However, I
> don't see an easy way to get around it in my case.  Can anyone suggest a
> good alternative?  Please note that I want to avoid relying on global
> variables such as 'SET TIMEZONE = ...' if possible, since those just
> introduce more potential for confusion, IMHO.

Why not simplify:

test=> select ('09.03.2014'||' '||lpad('3:00:00',8,'0'))::timestamp;
       timestamp
---------------------
  2014-09-03 03:00:00
(1 row)

test=> select ('09.03.2014'||' '||lpad('2:00:00',8,'0'))::timestamp;
       timestamp
---------------------
  2014-09-03 02:00:00
(1 row)

>
> Thanks!
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

gkhan <drjohnpayne@gmail.com> writes:
> Hi. I have a practical need to convert some badly-formatted date/times into
> 'timestamp without time zone' data types.  Like other scientists, I try to
> avoid timezone problems by sticking to UTC and using the 'timestamp without
> time zone' data type whenever possible.

> In this case, I used the to_timestamp() function as follows:
> SELECT to_timestamp('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')

I think you're wasting your time with to_timestamp.  The timestamp type
itself is perfectly capable of parsing this, and most other reasonable
inputs too.

regression=# set datestyle = dmy;
SET
regression=# select '09.03.2014 03:00:00'::timestamp;
      timestamp
---------------------
 2014-03-09 03:00:00
(1 row)

In particular, since what to_timestamp() returns is timestamp WITH time
zone, converting its result to timestamp WITHOUT time zone will cause a
timezone rotation which is what is messing you up.  If you feel you really
must do things this way, set the timezone parameter to "UTC" so there's no
zone conversion.

> In the recent thread "BUG #12739: to_timestamp function conver string to
> time incorrectly", tom lane suggests avoiding to_timestamp().  However, I
> don't see an easy way to get around it in my case.  Can anyone suggest a
> good alternative?  Please note that I want to avoid relying on global
> variables such as 'SET TIMEZONE = ...' if possible, since those just
> introduce more potential for confusion, IMHO.

You haven't provided one bit of convincing explanation as to why you
reject doing things in the multiple ways that will work, and insist on
doing it in a way that won't.

If your statement that you want to work exclusively in UTC isn't really
true, and you have a reason to want the global setting of TIMEZONE to be
something else, you could consider making a wrapper function that sets
TIMEZONE to UTC transiently while invoking to_timestamp and then coercing
its result to timestamp without time zone.  Something like

create function to_timestamp_utc(text, text) returns timestamp
  as $$ begin return to_timestamp($1, $2)::timestamp; end; $$
  language plpgsql
  strict
  immutable
  set timezone = utc;

            regards, tom lane

Thanks very much for both of your replies.  I had tried something similar and
gotten an error, so I am probably making a stupid mistake.  If I try this,
it works:

   SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
HH24:MI:SS')::timestamp

but if I use column names instead of the text, like this, it fails:
   SELECT (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')::timestamp
...

Both the gmt_date and gmt_time columns are "text" data type and formatted
exactly as in the original example, but I get this error:
   ERROR:  cannot cast type record to timestamp without time zone

I certainly would prefer "doing things in the multiple ways that will work,"
and I have probably just missed the obvious solution.  I work on wildlife
telemetry, and most GPS units and other satellite-linked devices report
times in UTC.  We often run into problems where someone plugs a laptop into
a piece of equipment and downloads data, and inadvertently sets the times to
a local time zone.  Therefore, we try to stick to UTC whenever collating
data from different sources.  However, when studying wildlife activity
patterns we are interested in local, biologically-meaningful times such as
sunrise and sunset, but not in daylight savings times, which are meaningless
to wildlife.  Therefore, most of us just add a fixed interval to UTC to
represent "local" times.




--
View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879738.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

...I meant to add, "and we therefore try to store all time and date values in
'timestamp without time zone' variables.



--
View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879739.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Oh sorry, what a dumb mistake!  ::timestamp works, of course!

Thanks



--
View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879746.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 12/31/2015 01:34 PM, gkhan wrote:
> Thanks very much for both of your replies.  I had tried something similar and
> gotten an error, so I am probably making a stupid mistake.  If I try this,
> it works:
>
>     SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')::timestamp
>
> but if I use column names instead of the text, like this, it fails:
>     SELECT (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')::timestamp
> ...
>
> Both the gmt_date and gmt_time columns are "text" data type and formatted
> exactly as in the original example, but I get this error:
>     ERROR:  cannot cast type record to timestamp without time zone
>
> I certainly would prefer "doing things in the multiple ways that will work,"
> and I have probably just missed the obvious solution.  I work on wildlife
> telemetry, and most GPS units and other satellite-linked devices report
> times in UTC.  We often run into problems where someone plugs a laptop into
> a piece of equipment and downloads data, and inadvertently sets the times to
> a local time zone.  Therefore, we try to stick to UTC whenever collating
> data from different sources.  However, when studying wildlife activity
> patterns we are interested in local, biologically-meaningful times such as
> sunrise and sunset, but not in daylight savings times, which are meaningless
> to wildlife.

But not necessarily to the interaction of wildlife with humans. As
someone who used to drive for a living I can tell you DST/ST moving
human activity backwards and forwards through biologically-meaningful
times has meaning to the critters.

> Therefore, most of us just add a fixed interval to UTC to
> represent "local" times.
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879738.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Follow-up:

My initial question was about oddly-formatted date/times.  The suggested
solution of casting directly to timestamp with ::timestamp is not as
flexible as the to_timestamp function that I was trying to avoid.  For
example, this fails because of the day-before-month format:

SELECT ('18.09.2015 18:01:40')::timestamp
--ERROR: date/time field value out of range

whereas this works, but results in a timestamp *with* time zone that makes
assumptions about daylight savings times:
SELECT to_timestamp('18.09.2015 18:01:40','DD.MM.YYYY HH24:MI:SS')

I ended up with this simple solution, which does what I wanted to and avoids
time zones:
SELECT (to_date('18.09.2015','DD.MM.YYYY') ||' '||'18:01:40')::timestamp

Adrian: thanks for your observation about wildlife-human interactions --
that is a useful reminder since I'll be looking at traffic patterns.



--
View this message in context: http://postgresql.nabble.com/to-timestamp-alternatives-tp5879723p5879753.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 12/31/15 5:05 PM, gkhan wrote:
> For
> example, this fails because of the day-before-month format:

Right, which is why Tom had in his example:

regression=# set datestyle = dmy;

BTW, my recommendation would be to store in a timestamptz field *with
the correct timezone*, and then convert on output as necessary. This is
easy to do by either

SET timezone

or

SELECT timestamptz_field AT TIME ZONE '...';

If you want the time without DST, you can just use a timezone like '+8'
or '-8'.

Since you're dealing with GPS data and presumably have lat/long, it
shouldn't be hard to do this dynamically either, either by just blindly
dividing longitude by 15 or using actual timezone shape polygons and @>
or <@.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/31/2015 03:05 PM, gkhan wrote:
> Follow-up:
>
> My initial question was about oddly-formatted date/times.  The suggested
> solution of casting directly to timestamp with ::timestamp is not as
> flexible as the to_timestamp function that I was trying to avoid.  For
> example, this fails because of the day-before-month format:
>
> SELECT ('18.09.2015 18:01:40')::timestamp
> --ERROR: date/time field value out of range

It it where me I would deal with this in the original data, either
pre-import or as part of the import process. Presumably for a given data
set the date/time format is the same and therefore more easily
converted. The goal would be to then have a 'standard' date/time output
format landing in the database. Seems easier then going back after the
fact and building a process for all eventualities.

>
> whereas this works, but results in a timestamp *with* time zone that makes
> assumptions about daylight savings times:
> SELECT to_timestamp('18.09.2015 18:01:40','DD.MM.YYYY HH24:MI:SS')
>
> I ended up with this simple solution, which does what I wanted to and avoids
> time zones:
> SELECT (to_date('18.09.2015','DD.MM.YYYY') ||' '||'18:01:40')::timestamp
>
> Adrian: thanks for your observation about wildlife-human interactions --
> that is a useful reminder since I'll be looking at traffic patterns.

Just part of a bigger observation that it is often assumed humans are
not animals.



--
Adrian Klaver
adrian.klaver@aklaver.com

gkhan schrieb am 31.12.2015 um 22:34:
> Thanks very much for both of your replies.  I had tried something similar and
> gotten an error, so I am probably making a stupid mistake.  If I try this,
> it works:
>
>     SELECT ('09.03.2014'||' '||lpad('3:00:00',8,'0'),'DD.MM.YYYY
> HH24:MI:SS')::timestamp
>
> but if I use column names instead of the text, like this, it fails:
>     SELECT (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')::timestamp
> ...
>
> Both the gmt_date and gmt_time columns are "text" data type and formatted
> exactly as in the original example, but I get this error:
>     ERROR:  cannot cast type record to timestamp without time zone

This expression:

     (gmt_date||' '||lpad(gmt_time),'DD.MM.YYYY HH24:MI:SS')


creates an anonymous object type with two columns, that's why you get the "cannot cast type record" error.

You only need to provide the text value to be casted (no format mask).
Use only a single expression without those unnecessary parentheses:

     SELECT gmt_date||' '||lpad(gmt_time,8,'0')::timestamp

You don't need the lpad() either:

     SELECT gmt_date||' '||gmt_time::timestamp

> On 01 Jan 2016, at 0:46, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
>
> BTW, my recommendation would be to store in a timestamptz field *with the correct timezone*, and then convert on
outputas necessary. This is easy to do by either 
>
> SET timezone
>
> or
>
> SELECT timestamptz_field AT TIME ZONE '…';

This. When converting the original timestamps to UTC, you lose data. In my experience, you will end up needing that
datasooner or later. 
Remember, the database stores timestamps in UTC internally anyway, you don't need to the conversion yourself.

> Since you're dealing with GPS data and presumably have lat/long, it shouldn't be hard to do this dynamically either,
eitherby just blindly dividing longitude by 15 or using actual timezone shape polygons and @> or <@. 

That would be a bad idea for global data, since not all time zones are full hours apart, or have the same (if any) DST
change-overdates. For example, India is currently at UTC+05:30, probably because they wrapped the entire country in the
sameTZ after their independence. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On 1/1/16 7:15 AM, Alban Hertroys wrote:
>> Since you're dealing with GPS data and presumably have lat/long, it shouldn't be hard to do this dynamically either,
eitherby just blindly dividing longitude by 15 or using actual timezone shape polygons and @> or <@. 
> That would be a bad idea for global data, since not all time zones are full hours apart, or have the same (if any)
DSTchange-over dates. For example, India is currently at UTC+05:30, probably because they wrapped the entire country in
thesame TZ after their independence. 

The original desire was to ignore DST, hence my suggestion. Normally
you'd want to figure out the correct TZ for the lat/long.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 1/1/16 4:11 AM, Thomas Kellerer wrote:
> You only need to provide the text value to be casted (no format mask).
> Use only a single expression without those unnecessary parentheses:
>
>      SELECT gmt_date||' '||lpad(gmt_time,8,'0')::timestamp
>
> You don't need the lpad() either:
>
>      SELECT gmt_date||' '||gmt_time::timestamp

I suspect you need to wrap that in (). Even if the parser does the right
thing there, it'd certainly make the intent a lot clearer.

SELECT (gmt_date || ' ' || gmt_time)::timestamp
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com