Thread: Create Timestamp From Date and Time
Hi, I'm converting an Access db to Postgresql and want to convert a DATE
startDate and a TIME startTime column into asingle TIMESTAMP startTime
field.
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 anyone enlighten me on what I'm doing wrong?
Thanks
BTW, this function is not listed in the 7.3rc2 documentation(?!)
--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com
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
"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
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:
> 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