[Resolved] comparing datetime stamps?

viveleroi0

I have two MySQL tables... one with the last date the user visited, and the one with the date of an incoming order.

What query could I use to count how many rows in table "Transactions" field "Trans_Date" have datetime entries more recent that the last datetime entered into table "Work_Detail" field "Order_Date"?

basically, I want a message to say "You have 5 new order since your last visit"

type deal

aaron_karp

Are you using the DATE field type or are you using UNIX timestamps?

viveleroi0

I am using DATETIME as the field type.

aaron_karp

Well, I don't know how to work specifically with those, but I'd like to recommend that you use UNIX timestamps for dates in the future. I switched over about a year ago and my life has never been easier (ok - my life regarding PHP dates. my girlfriend makes my life harder).

Instead of a DATETIME field, you use a regular INT type. Then you set the value to the UNIX timestamp for a given time (for example, March 25, 2004 at 5:45 PM is represented by 1079477100).

The integer represents the number of seconds since the "UNIX epoch" - yeah, it's kind of a geeky name - which is Jan 1 1970 at 0:00:00 or something like that. I could be a little off on the date.

PHP has all kinds of built in functions for manipulating these intergers into useful dates and times. date("M, Y", $timestamp) will return Month, Year for any given timestamp, etc. Check out php.net's definitions of date(), time() and mktime()

Best of all, for you case, comparing dates is as easy as using your regular > < operators. If one field has a greater integer than the other, you know it contains a date that is more recent.

Hope this helps. Switching over to timestamps is SO useful. I wrote this whole event based calendar using them, and it was a piece of cake.

-Aaron

aaron_karp

But to answer your original question......

"SELECT MAX(Order_Date) FROM Work_Detail WHERE (condition)"

That will give you the most recent order date (at least, if you're using timestamps....i'm not sure about the datetimes)

Then use that date in your next query. I'm sure there's a way to combine the queries, but I've got to run to class right now =)

$sql = "SELECT * FROM Transactions, Work_Detail WHERE Trans_Date > $most_recent_order_date";

$result = mysql_query($sql);

echo "You have " . mysql_num_rows($result) . "new orders since your last visit.";

Hope that works.

viveleroi0

I changed the field types to use the UNIX Epoch... but what now what kind of query can I use?

I tried:

SELECT * FROM Work_Detail LEFT JOIN Transaction ON Work_Detail.Order_View=Transaction.TRANS_DATETIME WHERE Work_Detail.Order_View <= Transaction.TRANS_DATETIME

But that doesn't work