I come from a world where databases are less SQL standards compliant. So as I was getting into PostgreSQL, I remember spending a lot of time learning about time. One of these time-consuming time investigations was troubleshooting my functions to figure out why I couldn’t seem to get more than one current timestamp value. I recently made some updates to a few procedures with logging, and could not remember the preferred way to accomplish current timestamps, so I went digging again. Since it always takes me more than a few moments to find this information, I thought I would post it here. This way I’ll remember it, and hopefully it will save some people the agony of searching the docs for a simple function or two.
The short answer is: you probably want to remove “now()” or “CURRENT_TIMESTAMP”, and replace it with “clock_timestamp()” or “timeofday()”.
The reference for this information is available in the docs (of course, rtfm) here: PostgreSQL 9.0.2 Documentation: Date/Time Functions and Operators.
Before I highlight some bits from the documenation, here is an example of our “problem”.
In MySQL - generate a function that depends on time progressing as it runs, notice that the current timestamps for ABC and DEF differ by approximately 10 seconds after running this function.
DROP PROCEDURE IF EXISTS test.testCurrentTimestamp$$ CREATE PROCEDURE test.testCurrentTimestamp() BEGIN
CREATE TABLE testTimestamp ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64), a_timestamp TIMESTAMP, PRIMARY KEY (id) );
INSERT INTO testTimestamp (name, a_timestamp) VALUES (‘CURRENT_TIMESTAMP A’,CURRENT_TIMESTAMP), (‘CURRENT_TIMESTAMP B’,CURRENT_TIMESTAMP), (‘CURRENT_TIMESTAMP C’,CURRENT_TIMESTAMP); SELECT SLEEP(10); INSERT INTO testTimestamp (name, a_timestamp) VALUES (‘CURRENT_TIMESTAMP D’,CURRENT_TIMESTAMP), (‘CURRENT_TIMESTAMP E’,CURRENT_TIMESTAMP), (‘CURRENT_TIMESTAMP F’,CURRENT_TIMESTAMP);
SELECT * FROM test.testTimestamp;
In PostgreSQL - generating an equivalent function for our tests results in something like the following. Notice that CURRENT_TIMESTAMP remains consistent with the timestamp at the beginning of the transaction (In MySQL you would persist this value by storing it manually):
– Function: testCurrentTimestamp()
– DROP FUNCTION testCurrentTimestamp();
CREATE OR REPLACE FUNCTION testCurrentTimestamp() RETURNS VOID AS
RAISE NOTICE ‘CURRENT_TIMESTAMP: %‘, CURRENT_TIMESTAMP; RAISE NOTICE ‘CLOCK_TIMESTAMP(): %‘, CLOCK_TIMESTAMP(); PERFORM PG_SLEEP(10); RAISE NOTICE ‘CURRENT_TIMESTAMP: %‘, CURRENT_TIMESTAMP; RAISE NOTICE ‘CLOCK_TIMESTAMP(): %‘, CLOCK_TIMESTAMP(); PERFORM PG_SLEEP(10); RAISE NOTICE ‘CURRENT_TIMESTAMP: %‘, CURRENT_TIMESTAMP; RAISE NOTICE ‘CLOCK_TIMESTAMP(): %‘, CLOCK_TIMESTAMP();
$BODY$ LANGUAGE ‘plpgsql’;
In many cases it won’t matter, because these efforts will generally be focused on logging and documentation, but it’s worth noting that “timeofday()” will return you the same time as “clock_timestamp()“. However, “timeofday()” returns the value as text, while “clock_timestamp()” returns a timestamp.
So, the basic rundown is: