Thread: Combine Date and Time Columns to Timestamp
My Web searching foo fails me, and I don't see the answer in the postgres docs so I hope someone here can point me in the proper direction. There is a table for bacteriological data that contains two columns for the date and time the water was collected and another two columns for the date and time the plates were read (since the latter should be less than 24 hours after the former). It would be simpler to combine each date-time pair into a single timestamptz column. Seems to me that this can be done with SQL within psql, but just how is not obvious to me because I've not worked with timestamp values before. What is the most parsimonious way to combine the two columns into one? TIA, Rich
On 01/18/2013 03:31 PM, Rich Shepard wrote: > My Web searching foo fails me, and I don't see the answer in the > postgres > docs so I hope someone here can point me in the proper direction. > > There is a table for bacteriological data that contains two columns for > the date and time the water was collected and another two columns for the > date and time the plates were read (since the latter should be less than 24 > hours after the former). It would be simpler to combine each date-time pair > into a single timestamptz column. Seems to me that this can be done with > SQL within psql, but just how is not obvious to me because I've not worked > with timestamp values before. > > What is the most parsimonious way to combine the two columns into one? How are they stored, as date and time type, strings, other? A sample of the data would help also. > > TIA, > > Rich > > > -- Adrian Klaver adrian.klaver@gmail.com
On Fri, 18 Jan 2013, Adrian Klaver wrote: > How are they stored, as date and time type, strings, other? Adrian, ISO date and time. > A sample of the data would help also. Example: 2012-10-29 | 10:19 | 2012-10-30 | 09:40 Rich
On 01/18/2013 04:26 PM, Rich Shepard wrote:
> On Fri, 18 Jan 2013, Adrian Klaver wrote:
>
>> How are they stored, as date and time type, strings, other?
>
> Adrian,
>
> ISO date and time.
>
>> A sample of the data would help also.
>
> Example: 2012-10-29 | 10:19 | 2012-10-30 | 09:40
test=> SELECT ('2012-10-29 '||'10:19')::timestamp;
timestamp
---------------------
2012-10-29 10:19:00
>
> Rich
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Jan 18, 2013, at 4:26 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> On Fri, 18 Jan 2013, Adrian Klaver wrote:
>
>> How are they stored, as date and time type, strings, other?
>
> Adrian,
>
> ISO date and time.
>
>> A sample of the data would help also.
>
> Example: 2012-10-29 | 10:19 | 2012-10-30 | 09:40
steven=# create temporary table date_test (d varchar, t varchar);
CREATE TABLE
steven=# insert into date_test values('2010-08-23', '8:04:33');
INSERT 0 1
steven=# select d::date + t::interval from date_test;
?column?
---------------------
2010-08-23 08:04:33
(1 row)
On 01/18/2013 04:26 PM, Rich Shepard wrote:
> On Fri, 18 Jan 2013, Adrian Klaver wrote:
>
>> How are they stored, as date and time type, strings, other?
>
> Adrian,
>
> ISO date and time.
>
>> A sample of the data would help also.
>
> Example: 2012-10-29 | 10:19 | 2012-10-30 | 09:40
Realized this would be a better form:
test=> SELECT ('2012-10-29'|| ' ' || '10:19')::timestamp;
>
> Rich
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
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.
Much appreciated,
Rich
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
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