SEO

#1 (**permalink**) 04-09-2008, 11:22 PM

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

#2 (**permalink**) 04-09-2008, 11:22 PM

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/s...TETIME-CURRENT

HTH,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3 (**permalink**) 04-09-2008, 11:22 PM

Hi Ray,

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:
> > 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/s...TETIME-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

#4 (**permalink**) 04-09-2008, 11:22 PM

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

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#5 (**permalink**) 04-09-2008, 11:22 PM

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

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#6 (**permalink**) 04-09-2008, 11:22 PM

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

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#7 (**permalink**) 04-09-2008, 11:22 PM

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

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

#8 (**permalink**) 04-09-2008, 11:22 PM

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

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

date and time