From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | gkhan <drjohnpayne(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: to_timestamp alternatives |
Date: | 2016-01-01 00:16:07 |
Message-ID: | 5685C547.2000503@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2016-01-01 02:36:41 | Happy New Year |
Previous Message | Jim Nasby | 2015-12-31 23:46:55 | Re: to_timestamp alternatives |