Thread: date and time
Hi,
i have a stored procedure (a function) in which i must generate a date/time stamp.
for that i use "select * from now();" and store the result into a column table.
is there a easier way to do that ? i tried to store directly now(); result but without success.
thx.
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
i have a stored procedure (a function) in which i must generate a date/time stamp.
for that i use "select * from now();" and store the result into a column table.
is there a easier way to do that ? i tried to store directly now(); result but without success.
thx.
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
On 24/03/2008 14:35, Alain Roger wrote: > for that i use "select * from now();" and store the result into a column > table. > > is there a easier way to do that ? i tried to store directly now(); > result but without success. Can you show us the full SQL statement? You could also use CURRENT_TIMESTAMP - look at the following: http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT HTH, Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
Hi Ray,
yes for sure. Here it is:
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
yes for sure. Here it is:
SELECT count(*) INTO existing_email FROM cust_portal.tmp_newsletterreg;
if (existing_email <>0) then
{
result = false;
}
else
{
result = true;
INSERT INTO cust_portal.tmp_newsletterreg VALUES
(
nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
email,
session,
SELECT CURRENT_TIMESTAMP;
)
}
end if;
RETURN(result);
On Mon, Mar 24, 2008 at 3:42 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 24/03/2008 14:35, Alain Roger wrote:Can you show us the full SQL statement?
> for that i use "select * from now();" and store the result into a column
> table.
>
> is there a easier way to do that ? i tried to store directly now();
> result but without success.
You could also use CURRENT_TIMESTAMP - look at the following:
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
HTH,
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
On Monday 24 March 2008 7:35 am, Alain Roger wrote: > Hi, > > i have a stored procedure (a function) in which i must generate a date/time > stamp. > for that i use "select * from now();" and store the result into a column > table. > > is there a easier way to do that ? i tried to store directly now(); result > but without success. > > thx. If you want the timestamp on INSERT add DEFAULT now() to column. In a pl/pgsql function I do; new.ts_update:=now() where ts_update is the column I am updating. Be aware now() records the time at the beginning of the transaction. An alternate is clock_timestamp() which records the current time. -- Adrian Klaver aklaver@comcast.net
Alain Roger wrote on 24.03.2008 15:45: >> SELECT count(*) INTO existing_email FROM cust_portal.tmp_newsletterreg; >> if (existing_email <>0) then >> { >> result = false; >> } >> else >> { >> result = true; >> INSERT INTO cust_portal.tmp_newsletterreg VALUES >> ( >> nextval( 'tmp_newsletterreg_nlreg_id_seq' ), >> email, >> session, >> SELECT CURRENT_TIMESTAMP; >> ) >> } >> end if; >> >> RETURN(result); That should be INSERT INTO cust_portal.tmp_newsletterreg VALUES ( nextval( 'tmp_newsletterreg_nlreg_id_seq' ), email, session, CURRENT_TIMESTAMP ) There is no SELECT and no semicolon inside the INSERT Thomas
On 24/03/2008 14:45, Alain Roger wrote: > INSERT INTO cust_portal.tmp_newsletterreg VALUES > ( > nextval( 'tmp_newsletterreg_nlreg_id_seq' ), > email, > session, > SELECT CURRENT_TIMESTAMP; > ) Hi Alain, That won't work; you just need ... values (.... email, session, CURRENT_TIMESTAMP) CURRENT_TIMESTAMP is a function, so it's return value will be used where it appears. What procedural language are you using? If it's pl/pgsql then the curly brackets aren't needed, nor are the parentheses in the return statement. Also, there's a semi-colon missing after the INSERT statement, which may or may not cause problems. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
So thanks a lot to everybody... so here is the result.
1. the semicolon was missing after the INSERT as wrote Raymond.
2. CURRENT_TIMESTAMP works great
3. i use pl/pgsql as language
thanks again.
Alain
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
1. the semicolon was missing after the INSERT as wrote Raymond.
2. CURRENT_TIMESTAMP works great
3. i use pl/pgsql as language
thanks again.
Alain
On Mon, Mar 24, 2008 at 3:56 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Alain Roger wrote on 24.03.2008 15:45:>> SELECT count(*) INTO existing_email FROM cust_portal.tmp_newsletterreg;That should be
>> if (existing_email <>0) then
>> {
>> result = false;
>> }
>> else
>> {
>> result = true;
>> INSERT INTO cust_portal.tmp_newsletterreg VALUES
>> (
>> nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
>> email,
>> session,
>> SELECT CURRENT_TIMESTAMP;
>> )
>> }
>> end if;
>>
>> RETURN(result);CURRENT_TIMESTAMP
INSERT INTO cust_portal.tmp_newsletterreg VALUES
(
nextval( 'tmp_newsletterreg_nlreg_id_seq' ),
email,
session,
)
There is no SELECT and no semicolon inside the INSERT
Thomas
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
Alain Roger wrote: > Hi, > > i have a stored procedure (a function) in which i must generate a > date/time stamp. > for that i use "select * from now();" and store the result into a > column table. > > is there a easier way to do that ? i tried to store directly now(); > result but without success. > Do you mean something like this: CREATE OR REPLACE FUNCTION "public"."test"() RETURNS timestamp AS $BODY$ DECLARE mydate_var timestamp; BEGIN --store the current timestamp in a variable mydate_var = now(); RETURN mydate_var; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Later, Tony Caduto AM Software http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL and MySQL