[Resolved] MIN/MAX on timestamp

[dreams4000]

Hi all, I am trying to pull a min and max timestamp and then get the difference in days between the two. The dates are coming from a list of dates.. I have used the below statement, but it doesnt seem to work right.

$sql2 = "SELECT ((MAX(UNIX_TIMESTAMP(checked_in)) - MIN(UNIX_TIMESTAMP(checked_out)))/86400) AND checked_in != '00000000000000' FROM sometable WHERE someid = $someid";

My MIN checked_out timestamp is : 20041003152859
My MAX checked_in timestamp is: 20041008152859

They are 5 days appart exactly so the output should be 5 days, but it outputs 1.. Any ideas?

[MrPixel]

SELECT (UNIX_TIMESTAMP(checked_out) - UNIX_TIMESTAMP(checked_in)) / 86400 AS days_apart
FROM my_table
WHERE id = $id;

[Sxooter]

The SQL compliant way is to use the extract() function:

select extract(days from '20040512 120000')-extract(days from '20040517 120000');

Most any modern database supports it.

[dreams4000]

Thanks for the info all! I used a way similar to pixel's but Sxooter, yours interests me and may give it a shot as well.. always like to learn new tricks. Thanks again!

[Sxooter]

The primary advantage to mine is that it should be more portable. At least amongst SQL 92 compliant databases. I find it a bit distressing how often folks uses Mysqlisms (or postgresqlisms for that matter) when a sql-92ism will work just as well AND can be ported from one RDBMS to another easily.