Thread: Automatic date/time

Hello all,

is it possible to have Pg automatically insert a Date or Time value on a
record if the INSERT comand does not include one ?

Something similar to AUTO INCREMENT for INTs ?!?!

This way the program that does the INSERT wouldn't have to worry about
the correct Date/Time value nor format....

Any hints ?

Thank you.

jmf

On 3/13/06 6:19 AM, "Joao Miguel Ferreira" <jmf@estg.ipvc.pt> wrote:

> Hello all,
>
> is it possible to have Pg automatically insert a Date or Time value on a
> record if the INSERT comand does not include one ?
>
> Something similar to AUTO INCREMENT for INTs ?!?!
>
> This way the program that does the INSERT wouldn't have to worry about
> the correct Date/Time value nor format....

Look at setting defaults and set the default to something like 'now()'.

Create table .... (

    mydate date default 'now()',

);

Sean

Hello,
To follow up on that, how to make sure the timestamp is always stored and
displayed at, for instance, time zone 'zulu'
Something like
create table ...
{
   datetime timestamptz default 'now() at time zone \'zulu\''
}
does not work; PostgreSQL complains about the syntax. Any idea.
Many thanks
JCR

"Sean Davis" <sdavis2@mail.nih.gov> wrote in message
news:C03AC283.7E86%sdavis2@mail.nih.gov...
>
>
>
> On 3/13/06 6:19 AM, "Joao Miguel Ferreira" <jmf@estg.ipvc.pt> wrote:
>
>> Hello all,
>>
>> is it possible to have Pg automatically insert a Date or Time value on a
>> record if the INSERT comand does not include one ?
>>
>> Something similar to AUTO INCREMENT for INTs ?!?!
>>
>> This way the program that does the INSERT wouldn't have to worry about
>> the correct Date/Time value nor format....
>
> Look at setting defaults and set the default to something like 'now()'.
>
> Create table .... (
>
>    mydate date default 'now()',
>
> );
>
> Sean
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

On Mar 13, 2006, at 22:37 , John Christopher wrote:

> To follow up on that, how to make sure the timestamp is always stored

Timestamps are stored as an absolute value, i.e., independent of time
zone offset. The time zone that is shown is the time zone of the
server. This is not something you define in DDL. (There has been
discussion of providing a method of actually saving the time zone
information as well, but it hasn't been implemented yet.) You can use
SET to change the time zone during the session:

http://www.postgresql.org/docs/current/interactive/sql-set.html

For example:
test=# select current_timestamp;
              now
------------------------------
2006-03-13 23:23:04.09736+09
(1 row)

test=# set time zone 'PST8PDT';
SET
test=# select current_timestamp;
               now
-------------------------------
2006-03-13 06:23:27.408327-08
(1 row)

or use the AT TIME ZONE construct, depending on what you want to do.

http://www.postgresql.org/docs/current/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

test=# select current_timestamp at time zone 'UTC';
           timezone
----------------------------
2006-03-13 14:24:14.901873
(1 row)


Michael Glaesemann
grzm myrealbox com

On Mon, Mar 13, 2006 at 08:37:16 -0500,
  John Christopher <jcxxr@yahoo.com> wrote:
> Hello,
> To follow up on that, how to make sure the timestamp is always stored and
> displayed at, for instance, time zone 'zulu'
> Something like
> create table ...
> {
>    datetime timestamptz default 'now() at time zone \'zulu\''
> }
> does not work; PostgreSQL complains about the syntax. Any idea.

You need to do that in the queries that are referencing the timestamps.