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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
