Thread: insert a SYSTIMESTAMP value in postgres
Hello,
I am trying to re-create few objects from oracle into postgres.
I have a problem inserting a timestamp value into the table:
insert into request_queue (request_id, received_time
)
values (new.request_id, SYSTIMESTAMP
);
How can I insert a dynamic timestamp value in postgress, please?
Thank you,
Leon
leon.match@convergia.net
On Jun 20, 2011, at 1:32 PM, Leon Match wrote: > How can I insert a dynamic timestamp value in postgress, please? < http://www.postgresql.org/docs/9.0/static/functions-datetime.html> -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Please look at the section on Date/Time Functions in the PostgreSQL documentation (Section 9.9 in Version 9.0 documentation) for a full listing of the date and time functions available in PostgreSQL and how they work. There is a sub-section (9.9.4) that specifically details functions that return the current time. Feel free to post with whichever function you feel is the correct one if you are still unsure after looking at the available options.
Your conversion from Oracle to PostgreSQL is going to go VERY slowly if you are going to ask a question for every syntax or feature that exists in Oracle that is different in PostgreSQL. You need to understand WHAT the original code is doing and then at least look for what feature/syntax in PostgreSQL will accomplish the same result.
Personally I can see where the missing MERGE feature maybe warrants a list posting but, really, getting the “Current Time” from a function call is basic functionality that you can be expected to be readily found in the PostgreSQL documentation if you had attempted to look.
There are some articles in the WIKI about database migrations that may be worth a read as well – though I have not perused them myself.
No disrespect intended, and the community appreciates newcomers, but please at least look through the extensive documentation before posting any more “how do I do this in PostgreSQL” questions. Then, if you still need to post, try describe what you are trying to accomplish WITHOUT resorting to posting Oracle code (code is OK but odds are many/most people on this list have never used Oracle). In this instance, “SYSTIMESTMP” looks like a function but we do not know for sure. And does it return a clock-time or a session-time?
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Leon Match
Sent: Monday, June 20, 2011 3:33 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] insert a SYSTIMESTAMP value in postgres
Hello,
I am trying to re-create few objects from oracle into postgres.
I have a problem inserting a timestamp value into the table:
insert into request_queue (request_id, received_time
)
values (new.request_id, SYSTIMESTAMP
);
How can I insert a dynamic timestamp value in postgress, please?
Thank you,
Leon
On Mon, 20 Jun 2011 15:32:31 -0400, Leon Match wrote:
Hello,
I am trying to re-create few objects from oracle into postgres.
I have a problem inserting a timestamp value into the table:
insert into request_queue (request_id, received_time
)
values (new.request_id, SYSTIMESTAMP
);
How can I insert a dynamic timestamp value in postgress, please?
Thank you,
Leon
leon.match@convergia.net
May be: insert into request_queue (request_id, received_time) values (new.request_id, (abstime(('now'::text)::timestamp(6) with time zone))::integer);
-- Regards, Condor
El 20/06/2011 14:33, "Leon Match" <leon.match@convergia.net> escribió:
>
> Hello,
>
>
>
> I am trying to re-create few objects from oracle into postgres.
>
>
>
> I have a problem inserting a timestamp value into the table:
>
>
>
> insert into request_queue (request_id, received_time
>
> )
>
> values (new.request_id, SYSTIMESTAMP
>
> );
>
>
>
> How can I insert a dynamic timestamp value in postgress, please?
>
Maybe this document can help you
http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
--
Jaime Casanova www.2ndQuadrant.com
Thanks a lot for your help!
The correct sentence to my solution is this:
insert into request_queue (request_id, received_time
)
values (new.request_id, LOCALTIMESTAMP
);
Regards,
Leon
-----Original Message-----
From: Osvaldo Kussama [mailto:osvaldo.kussama@gmail.com]
Sent: Monday, June 20, 2011 3:45 PM
To: Leon Match
Subject: Re: [GENERAL] insert a SYSTIMESTAMP value in postgres
2011/6/20 Leon Match <leon.match@convergia.net>:
> Hello,
>
> I am trying to re-create few objects from oracle into postgres.
>
> I have a problem inserting a timestamp value into the table:
>
> insert into request_queue (request_id, received_time)
> values (new.request_id, SYSTIMESTAMP);
>
> How can I insert a dynamic timestamp value in postgress, please?
>
In Oracle/PLSQL, the systimestamp function returns the current system
date and time (including fractional seconds and time zone) on your
local database.
In PostgreSQL see the manual:
http://www.postgresql.org/docs/current/interactive/functions-datetime.html#F
UNCTIONS-DATETIME-CURRENT
"CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone.
PostgreSQL also provides functions that return the start time of the
current statement, as well as the actual current time at the instant
the function is called. The complete list of non-SQL-standard time
functions is:
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()
transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
named to clearly reflect what it returns. statement_timestamp()
returns the start time of the current statement (more specifically,
the time of receipt of the latest command message from the client).
statement_timestamp() and transaction_timestamp() return the same
value during the first command of a transaction, but might differ
during subsequent commands. clock_timestamp() returns the actual
current time, and therefore its value changes even within a single SQL
command."
Osvaldo