timestamp default values

From: Brendan Jurd <direvus(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: timestamp default values
Date: 2005-08-06 04:46:01
Message-ID: 37ed240d05080521467c2e040c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I have a table which is used for logging, and I want a timestamp
column which reliably stores the insert time for each row inside a
transaction, with maximum precision.

Now, if I'm reading the documentation
(http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT)
correctly, the only way to get the current time inside a transaction
is to use timeofday().

timeofday() returns text, and moreover it returns in a bizarre format
which cannot be converted directly into any useful temporal types, at
least not in 8.0.2:

=> select timeofday();
timeofday
-------------------------------------
Sat Aug 06 14:41:49.596859 2005 EST
(1 row)

=> select timeofday()::timestamp;
ERROR: invalid input syntax for type timestamp: "Sat Aug 06
14:41:57.875478 2005 EST"

=> select timeofday()::date;
ERROR: invalid input syntax for type date: "Sat Aug 06
14:43:41.672518 2005 EST"

So, if I'm on the right track here, the only way to really get the
value I want is to do something like:

DEFAULT to_timestamp(timeofday(), 'Dy Mon DD HH24:MI:SS.US YYYY')

Does this strike anybody else as circumlocutive?

From the aforementioned manual page:

It is important to know that CURRENT_TIMESTAMP and related functions
return the start time of the current transaction; their values do not
change during the transaction. This is considered a feature: the
intent is to allow a single transaction to have a consistent notion of
the "current" time, so that multiple modifications within the same
transaction bear the same time stamp. timeofday() returns the
wall-clock time and does advance during transactions.

I agree that being able to reference the time the transaction started
is a useful feature, but it should not be made available at the
expense of being able to reference the actual time. Terms like "now"
and "current timestamp" seem unambiguous to me -- they are misleading
names for the transaction start time.

At least, there should be a function that really does return the
current timestamp.

--
BJ

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-08-06 04:47:00 Re: How to join function with a table?
Previous Message Tom Lane 2005-08-06 04:38:50 Re: DNS vs /etc/hosts