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
