Grabbing 2 dates from an SQL database and then comparing them?

HairyArse

Hi, I have a query:

SELECT P.Delivery, D.Date_Delivered
FROM products P, deliveries D
WHERE P.Nsn = '$Nsn'
AND D.Nsn = '$Nsn'
LIMIT 0 , 30

Which captires two dates from my sql database, what is the PHP code to send these to two separate variables and then compare them to see if the second is greater than the first?

kubis

SELECT CASE WHEN datecol1 > datecol2 THEN 1 ELSE 0 END AS datecol1_is_bigger FROM tablename

there is no need to select the two dates and compare them in php (except the case you need the dates anyway; even here i would select the datecol1, datecol2 and the datecol1_is_bigger)

HairyArse

Kubis, can you explain what that code does?

Also, I think I do need to pass the values to PHP as I want to work out the difference between the two dates and depending on whether the value is + or - this will determine the formatting of a table row. I.e. Red for - and green for +.

Weedpacket

I'd say the query sets the value of datecol1_is_bigger to 1 if the value in the field named datecol1 (in the table named "tablename") is greater than (i.e., after) the value in datecol2 (in the same table) and 0 otherwise. (In other words, it pretty much does what it says.)

It could no doubt be extended to do -1, 0, and +1 by writing

SELECT CASE WHEN datecol1 > datecol2 THEN 1 WHEN datecol1 < datecol2 THEN -1 ELSE 0 END AS compare_datecol1_datecol2 FROM tablename

HairyArse

That sounds good in practice, but the two dates are in different tables, and this doesn't tell me how many days are between each date, which is what I need.

kubis

Originally posted by HairyArse
Kubis, can you explain what that code does?

Sure.
CASE WHEN condition THEN result1 ELSE result2 END is the same as switch() in PHP so the query means:
if datecol1 is bigger datecol2 then return 1; if not, return 0 as column named datecol1_is_bigger.

with:

SELECT datecol1, datecol2, CASE WHEN datecol1 > datecol2 THEN 1 ELSE 0 END AS datecol1_is_bigger FROM tablename

you will get a row with 3 columns. datecol1 (is a date), datecol2 (is a date) and datecol1_is_bigger (returns 0 or 1 as a result of the two dates comparation)

If you need to pass the column dates to PHP and the database engine is MySQL, you can use:

select UNIX_TIMESTAMP(datecol1) AS datecol1uts, UNIX_TIMESTAMP(datecol2) AS datecol2uts....

using the unix_timestamp() function the mysql converts date representation to unix timestamp (also the same kind of date representation you get when you use time() function in PHP).

in the case you are not using mysql, but for example postgres or oracle, use to_char(), like to_char(datecol1, 'YYYY-MM-DD HH24:MI:SS') AS datecol1date; then in PHP use strtotime($result['datecol1date']) and voila 😉

kubis

Originally posted by HairyArse
That sounds good in practice, but the two dates are in different tables, and this doesn't tell me how many days are between each date, which is what I need.

SELECT CASE WHEN tablename1.datecol > tablename2.datecol THEN 1 ELSE 0 END AS datecol1_is_bigger FROM tablename1, tablename2 WHERE tablename1.id = tablename2.someforeignkey (or another crazy join expression)

kubis

Originally posted by HairyArse
That sounds good in practice, but the two dates are in different tables, and this doesn't tell me how many days are between each date, which is what I need.

if you are using MySQL, then consider the use of functions to_days() or timediff() etc.

and learn to use the manual http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html