SQL Puzzler: now() vs time() = ?
This puzzler is not a clean SQL puzzler, as it is related not only to the SQL function now(), but to a PHP function time() too. Are you accessing PostgreSQL from PHP? In that case you should solve this puzzler easily ;-)
Consider this very simple table
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
inserted TIMESTAMP(0) NOT NULL DEFAULT now(),
my_value INTEGER
);
and run the following PHP script:
<?php
// connection to the DB server
$conn = pg_connect(...);
// insert a row
$sql = 'INSERT INTO my_table (my_value) VALUES (1)';
pg_execute($conn, $sql);
// read the "historic" records
$sql = 'SELECT COUNT(*) AS c FROM my_table ' .
"WHERE inserted < '" . date('Y-m-d G:i:s') . "'";
$res = pg_query($sql);
$row = pg_fetch_assoc($res);
// print the count
echo 'count: ',$row['c'];
pg_close($conn);
?>
Suppose the table was empty before executing the script - what is the result?
As usually, I'll offer you several options:
- 0
- 1
- 2
- none of the above
The answer is listed in the following section - the answer is intentionally hidden so you may not see it by accident. Just click the "Answer" and it will appear.
Answer
The correct answer is (d) - the script will print "0" in about half the time, and "1" in the other half. The problem is in how the functions now() and date() handle fractions of the seconds.
When processing the results of the SQL function "now()" because of the TIMESTAMP(0) data type, the values are actually rounded. On the other side the "date()" function just calls a "time()" function and thus just truncates the fractions of second.
| now() | now()::timestamp(0) | date('Y-m-d G:i:s') |
| 2010-03-26 02:08:46.37512+01 | 2010-03-26 02:08:46 | 2010-03-26 02:08:46 |
| 2010-03-26 02:08:46.83855+01 | 2010-03-26 02:08:47 | 2010-03-26 02:08:46 |
And this is exactly the root of the problem illustrated by this puizzler - if you hit the second half of a second, the function "now()" returns the "next second" but the PHP function "date()" returns the current second. Thus the query unexpectedly prints "0".
But if you hit the first half of a second, everything works as expected and the script prints "1."
So let's see at least one way to fix this problem - e.g. you may stop using the default "date" without the second (optional) parameter, and use a "microtime" function like this:
...
$time = round(microtime(true));
// read the "historic" records
$sql = 'SELECT COUNT(*) AS c FROM my_table ' .
"WHERE vlozeno < '" . date('Y-m-d G:i:s', $time) . "'";
$res = pg_query($sql);
$row = pg_fetch_assoc($res);
...
This way both values will be rounded the same way, and the script will always print "1" ;-)
