Create Timestamp From Date and Time

Ron St.Pierre wrote:

The 7.2 documentation lists a function:
timestamp(date '1998-02-24',time '23:07')
to do just this, however when I try to run it without hard coding the
date and time fields it fails.

Can you givean example of the statement you use when "not hard coding"?

Jc

Example - when I run the following:
select timestamp(date '1998-02-24',time '23:07')
I get the following error:
parse error at or near "date"

I am migrating a DB from Access to postgreSQL and want to convert two
fields from one table into one field (dtmStartDate + dtmStartTime into
startTimestamp). I am running a script after migrating to make many
other changes as well. However, I thought I would try the timestamp
function with the data and time "hard coded" just to see if my use of
the function was correct.

Jean-Christian Imbeault wrote:

The 7.2 documentation lists a function:
timestamp(date '1998-02-24',time '23:07')
to do just this, however when I try to run it without hard coding

the date and time fields it fails.

Can you givean example of the statement you use when "not hard coding"?

Jc

--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com

"Ron St.Pierre" <rstpierre@syscor.com> writes:

The 7.2 documentation lists a function:
timestamp(date '1998-02-24',time '23:07')
to do just this, however when I try to run it without hard coding the
date and time fields it fails.

Please be more specific: what did you write, what error did you get?

regards, tom lane

Ron St.Pierre wrote:

Example - when I run the following:
select timestamp(date '1998-02-24',time '23:07')
I get the following error:
parse error at or near "date"

what you want is:

select to_timestamp('1998-02-24 23:07'::text, 'YYYY-MM-DD HH:MI');
to_timestamp
------------------------
1998-02-24 23:07:00+09
(1 row)

Have a look at:

http://www.postgresql.org/idocs/index.php?functions-formatting.html

Jc

"Ron St.Pierre" <rstpierre@syscor.com> writes:

Example - when I run the following:
select timestamp(date '1998-02-24',time '23:07')
I get the following error:
parse error at or near "date"

"timestamp" is a reserved word these days, so to use it as a function
name you must double-quote it.

regression=# select timestamp(date '1998-02-24',time '23:07');
ERROR: parser: parse error at or near "date" at character 18
regression=# select "timestamp"(date '1998-02-24',time '23:07');
timestamp
---------------------
1998-02-24 23:07:00
(1 row)

Kinda messy, but the alternative of choosing a different name for this
function doesn't seem very palatable either...

regards, tom lane

REPOST -> with correct email address

Ron wrote:
Thanks, that works but I am still having problems extracting both the
date and time from a table and converting them into a timestamp,
specifically with the 'time' column. Here's an example of the data:
cntuserid | dtmstartdate | dtmstarttime
-----------+------------------------+---------------------
2119 | 2000-05-10 00:00:00-07 | 1899-12-30 19:32:33
2119 | 2000-05-10 00:00:00-07 | 1899-12-30 19:36:30

I want to combine the 'date' part of 'dtmstartdate' with the 'time'
portion of 'dtmstarttime', dynamically, to form something like:
cntuserid | dtmstart -----------+------------------------
2119 | 2000-05-10 19:32:33
2119 | 2000-05-10 19:36:30

Thanks to previous help I can get the date, but I just don't know how to
get the time to work. I've tried RTFM, the web, discussion groups, tried
functions, etc

BTW I'm migrating an Access db to PostgreSQL v 7.2.1 (soon to be 7.3). I
also don't care whether the result is timestamp or timestamptz, both
would work. Thanks

Tom Lane wrote:

"Ron St.Pierre" <rstpierre@syscor.com> writes:

Example - when I run the following:
select timestamp(date '1998-02-24',time '23:07')
I get the following error:
parse error at or near "date"

"timestamp" is a reserved word these days, so to use it as a function
name you must double-quote it.

regression=# select "timestamp"(date '1998-02-24',time '23:07');
timestamp
---------------------
1998-02-24 23:07:00
(1 row)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com

On Wed, Dec 04, 2002 at 04:24:37PM +0000, Ron St.Pierre wrote:

REPOST -> with correct email address

Ron wrote:
Thanks, that works but I am still having problems extracting both the
date and time from a table and converting them into a timestamp,
specifically with the 'time' column. Here's an example of the data:
cntuserid | dtmstartdate | dtmstarttime
-----------+------------------------+---------------------
2119 | 2000-05-10 00:00:00-07 | 1899-12-30 19:32:33
2119 | 2000-05-10 00:00:00-07 | 1899-12-30 19:36:30

I want to combine the 'date' part of 'dtmstartdate' with the 'time'
portion of 'dtmstarttime', dynamically, to form something like:
cntuserid | dtmstart -----------+------------------------
2119 | 2000-05-10 19:32:33
2119 | 2000-05-10 19:36:30

Thanks to previous help I can get the date, but I just don't know how to
get the time to work. I've tried RTFM, the web, discussion groups, tried
functions, etc

BTW I'm migrating an Access db to PostgreSQL v 7.2.1 (soon to be 7.3). I
also don't care whether the result is timestamp or timestamptz, both
would work. Thanks

create table addtime (usedate timestamp, usetime timestamp);

insert into addtime values ('2002-01-01 9:00 AM', '2002-01-05 10:30
AM');

select date(usedate) + cast(usetime as time with time zone) from
addtime;

--

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant