Yesterday and today date in mysql

Question

My date column in my database is in this format: date('Y-M-j H:i:s');. A am trying to run a query to get all transactions done today and another one for all transactions done yesterday. Please help on best way to do this.

I tried the below but it wasn't working

 $yesterday = date("Y-M-j 00:00:01", strtotime("-1 day")); $yesterday2 = date("Y-M-j 23:59:59", strtotime("-1 day")); $query_syesterday = "SELECT sum(amount), sum(amtnaira) FROM transactions WHERE transtype = 'buy' AND batch !='' AND date BETWEEN '$yesterday' AND '$yesterday2'"; $syesterday = mysql_query($query_syesterday, $egold1) or die(mysql_error()); $row_syesterday = mysql_fetch_assoc($syesterday); $totalRows_syesterday = mysql_num_rows($syesterday);

how about NOW() - INTERVAL 1 DAY and NOW() + INTERVAL 1 DAY

user557846
– user557846

2012-09-04 23:53:26 +00:00
Commented Sep 4, 2012 at 23:53 — user557846
– user557846, Commented Sep 4, 2012 at 23:53

wesside · Accepted Answer · 2012-09-05 00:28:43Z

This should do it:

$query_last_two_days = "SELECT sum(amount), sum(amtnaira) FROM transactions WHERE transtype = 'buy' AND batch !='' AND date < DATE_SUB(NOW(), INTERVAL 2 DAY)";

You can get the last two days easily like this. You can change NOW() to a date of your choosing.

Petah · Accepted Answer · 2012-09-04 23:40:51Z

You are using the wrong date format. Use this instead:

$yesterday = date("Y-m-d", strtotime("-1 day"));

Also, just compare the date, rather than the time:

SELECT SUM(amount), SUM(amtnaira) FROM transactions WHERE transtype = 'buy' AND batch != '' AND DATE(date) = $yesterday

Moe Tsao · Accepted Answer · 2012-09-04 23:44:22Z

1

Try this:

$yesterday= date("Y-m-d", time()-86400)." 00:00:00"; $yesterday2= date("Y-m-d", time()-86400)." 23:59:59";

edited Sep 4, 2012 at 23:44

answered Sep 4, 2012 at 23:39

Moe Tsao

1,0546 silver badges9 bronze badges

3 Comments

zerkms Over a year ago

@Nile: echo isn't helpful in such cases, var_dump is. Compare with ideone.com/k2Tzb

Moe Tsao Over a year ago

Not sure what my finger is doing... It just happened. Edited to take out the strtotime part

jeremy Over a year ago

@zerkms what do you mean? I was pointing out the irony... never mind now, he fixed it. how was using strtotime() there correct?

Collectives™ on Stack Overflow

Yesterday and today date in mysql

3 Answers 3

Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Related