homepage Welcome to WebmasterWorld Guest from 23.22.173.58
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
CURRENT_DATE() with DATETIME
How do grab entries from yesterday...
wfernley

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 278 posted 5:17 pm on Jan 26, 2006 (gmt 0)

Hey everyone,

I'm still fairly new to MySQL's DATETIME.

I have a table that records conversions for the day. With the entries it records the DATETIME of the conversion with the NOW() function. The date looks like this : 2006-01-26 10:43:51

What is the SQL for grabbing entries from yesterday?
Currently I am trying:
SELECT * FROM conversions WHERE date = CURRENT_DATE() - 1

The SQL query returns nothing with no error even though there are conversions yesterday.

Can anyone help?

Thanks in advance for your replies!

Wes

 

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 278 posted 5:49 pm on Jan 26, 2006 (gmt 0)

You want

SELECT * FROM conversions WHERE date = CURRENT_DATE() - INTERVAL 1 DAY;

There are other arguments you can do with this, some examples:

SELECT now() + INTERVAL 1 SECOND;

SELECT now() - INTERVAL 1 SECOND;

SELECT now() - INTERVAL 1 MONTH;

SELECT now() - INTERVAL 1 YEAR;

I will sticky you the mysql help page for this, not sure the forum moderator will want the link posted

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 278 posted 6:52 pm on Jan 26, 2006 (gmt 0)

MySQL is an authoritative site so it is OK to link there.

[dev.mysql.com...]

wfernley

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 278 posted 7:16 pm on Jan 26, 2006 (gmt 0)

Thank you for your replies and link to the MySQL page on Date/Timestamp.

Using SELECT * FROM conversions WHERE date = CURRENT_DATE() - INTERVAL 1 DAY; does not work :S

Just like the other SQL query I mentioned, it says there are no entries from yesterday. Should I work around it using the PGP date function? I know how I can do that but I wanted to get the MySQL date working because its something new ;)

Any other suggestions?

Thanks again!

Wes

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 278 posted 8:56 pm on Jan 26, 2006 (gmt 0)

The problem may be the fact that there are times on your dat so it doesn't match yeterday.

Try this

SELECT
*
FROM
conversions
WHERE
date >= CURRENT_DATE():00:00:00 - INTERVAL 1 DAY
and date <= CURRENT_DATE():12:59:59 - INTERVAL 1 DAY;

The syntax may be off but the basic idea is you want to see if your dateTime falls in the time range for yesterday. There may be a CURRENT_DATE().earliestTime() or CURRENT_DATE().latestTime() funtion that will return the latet and erliest times of a day but I am not sure.

wfernley

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 278 posted 9:16 pm on Jan 26, 2006 (gmt 0)

Thank you Demaestro.

I will try that out. I just got it working but I cheaped out and made two cells, date and time. It's working now but I'm having to use an extra cell.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 278 posted 10:50 pm on Jan 26, 2006 (gmt 0)

It is cheap but it'll work.

It will suck for ordering by date and time though if they are seperate fields. It will also make comparisons harder if you split them. Like when you want to query things that come before or after something else based on date and time. Because if it has to consider the time then it is a little bit of a hassle. You have to make sure the date fields are equal, then you can check if the time is before or after a time you need to compare against.

Just a heads up of a few pit falls of splitting your datetime field into 2 seperate fields.

wfernley

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 278 posted 1:46 pm on Jan 27, 2006 (gmt 0)

Yeah I understand that ;)

I think they only ordering I will have to do will be simple ordering so it should be alright using the two seperate fields

Thanks again for your help!

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 278 posted 7:45 pm on Jan 27, 2006 (gmt 0)

No problem. I am happy to help with any SQL questions the geek in me loves it.

Radek_Cvek



 
Msg#: 278 posted 8:20 pm on Jan 27, 2010 (gmt 0)

Today:
SELECT * FROM conversions WHERE DAY( date ) = EXTRACT(DAY FROM NOW() )

Yesterday:
SELECT * FROM conversions WHERE DAY( date ) = EXTRACT(DAY FROM (NOW() - INTERVAL
1 DAY ) )

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved