Problem Querying database with unix timestamp

noimad1

Ok,

So I've used strtotime("now") to generate a unix-timestamp, and place that in my database. Now, I want to query my database based on that timestamp.

However, this pulls 0 records:

SELECT * FROM users WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= start_date;

According to the mysql documentation that should work.

Now, is this not working the way I think it should be? Does strtoftime not give the correct unix timestamp to use for this query?

NogDog

What type of field is start_date?

noimad1

The start_date is an integer.

However, I finally was able to get it to work with this query:

SELECT
COUNT(*) AS Today
FROM
users
WHERE
(start_time > unix_timestamp(date_sub(current_timestamp(), interval 1 day)))

However, that interval actually goes back a full day (24hours). I guess now I need to figure out how to get it to only query for today. Basically starting today at midnight, up until whatever point in time they do the query...

noimad1

Ok, here is what I really need to do.

I need to run a report on my database to show sales for the past 7 days, broken down by day.

So for example, I would want to show:

monday, tuesday, wed. etc.

And display how many sales each day had.

So I'm trying to build mysql queries to only show sales for a specific day.

I've looked through all of the mysql date functions and can't seem to figure this out.

I can only do mysql queries because I am using some "php builder" software the client used to build their application to begin with. So I can't add any additonal php to this.

Is there any way I can accomplish this. I guess it would look something like this:

SELECT
COUNT(*) AS MONDAY
FROM
users
WHERE
(start_time = "LAST_MONDAY")

But of course start_time is stored as a unix timestamp. So how can I use the mysql date/time to get these 7 queries to work?

NogDog

You could do something like:

$last_monday = date('Y-m-d', strtotime('last Monday'));
$sql = "SELECT . . . WHERE DATE(FROM_UNIXTIME(start_time)) = '$last_monday'";