[RESOLVED] timestamp / unix time stamp querying...

stevesweetland

Im baffled, officially!

Okay.

I have a table.

ORDERS

ID
DateTime
Value

DateTime is generated by PHP and is a UNIX timestamp. I need to return all the orders, that were placed, say, between 4-5pm, on whatever day.

I see a few functions that should help me, FROM_TIMESTAMP, HOUR, INTERVAL...

but um... i just cant seem to crack how to do it... can someone just boot me in the right direction?

SpeedBird

Something like

$result = mysql_query("SELECT id, datetime, value FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) = DATE(FROM_UNIXTIME(datetime))");

would give you all records with a timestamp 7 days prior to the current date. You could try setting the interval using variables in your PHP code.

stevesweetland

SpeedBird wrote:
Something like

$result = mysql_query("SELECT id, datetime, value FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) = DATE(FROM_UNIXTIME(datetime))");

would give you all records with a timestamp 7 days prior to the current date. You could try setting the interval using variables in your PHP code.

would it be possible to do this on a recurring basis... i mean...

if i said INTERVAL 1 DAY would it return me todays and yesterdays, or all of the instances that are 1 day apart?

ideal world : if i did INTERVAL 7 DAY, it would return me instances from 7 days ago, 14 days ago, 21 days ago, etc....

SpeedBird

No, you would only see orders from 7 days prior to the current date. You would need an OR statement in the query to select records for 14, 21, 28 days prior etc:

$result = mysql_query("SELECT id, datetime, value FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) = DATE(FROM_UNIXTIME(datetime)) OR DATE_SUB(CURDATE(),INTERVAL 14 DAY) = DATE(FROM_UNIXTIME(datetime))");

That said, the above query can be modified to use most (if not all) of all the date/time functions in MySQL - clicky.

stevesweetland

ok... gave up hope for this one.

added fields for hour, day, month, and year.... these are created when i run the report script which im writing.

this allows me to break down orders by time of day, day, and month... so you can essentially get an overview of what times are busiest, what days are busiest etcetera.