Combine Date and Time Columns to Timestamp

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general