Re: Combine Date and Time Columns to Timestamp

On 20/01/13 04:40, Kevin Grittner wrote:
> Rich Shepard wrote:
>> On Fri, 18 Jan 2013, Adrian Klaver wrote:
>>
>>> test=> SELECT ('2012-10-29 '||'10:19')::timestamp;
>>> timestamp
>>> ---------------------
>>> 2012-10-29 10:19:00
>> Thanks, Adrian. I suspected it was simple but I could not find a reference
>> to the syntax.
> Of course, since you appear to want to deal with moments in time,
> timestamptz is more appropriate than just timestamp. If the values
> are in UTC, then you will want to append that to the string. You
> might use something like:
>
> test=# SELECT ('2012-10-29 '||'10:19'||' +00')::timestamptz;
> timestamptz
> ------------------------
> 2012-10-29 05:19:00-05
> (1 row)
>
> If they're not already in UTC and your locale has a seasonal offset
> like Daylight Saving Time, you might want to be careful with how
> you handle data around the autumnal shift, or you could have things
> which finish before they started.
>
> -Kevin
>
>
Yes, timestamptz is definitely to be preferred!

I once took a flight that landed 5 minutes before we took off, according
to the schedule, but the duration was positive 55 minutes - as we flew
across a time zone boundary.

Cheers,
Gavin