Обсуждение: date and time

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
---------------------------------------------------------------

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 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
---------------------------------------------------------------

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