MySQL Date Queries

[SalientAnimal]

Hi there guys,

Very simple question here. I am writing backend coding in m PHP file to extract data from the database, but would like to write queries to extract data forr a certain date ranges. How would I write queries to extract date for example:


Current Date -1
Current Date -3
Current Date -7
Current Date -14
Current Date -30
Current Month -1
etc...

Thnx

[Dan Grossman]

Check out the MySQL manual's Date and Time Functions page for lots of useful stuff:
http://dev.mysql.com/doc/refman/5.1/...functions.html

Basically, use DATE_SUB and intervals

SELECT * FROM table WHERE date > DATE_SUB(CURRENT_TIMESTAMP, interval 7 day)

[r937]

i'd use CURRENT_DATE instead of CURRENT_TIMESTAMP in this scenario, dan

[Immerse]

On the same subject, is it considered bad form to do the following?

SELECT some_field FROM some_table WHERE date > DATE_ADD(NOW(), interval -7 day)

I mean the -7 day interval (as opposed to using DATE_SUB).
It does work, but I'm wondering if it's correct

[r937]

interesting that you should make a distinction between "works" and "correct"

very often an SQL query will "work" (i.e. not produce a syntax error) but give the wrong answer

me personally, i prefer to do it this way --

... WHERE datecolumn > CURRENT_DATE - INTERVAL '7' DAY

the syntax used here is not specific to mysql (whereas DATE_ADD, DATE_SUB, and NOW are all proprietary mysql syntax)

furthermore, as i said earlier, i'd use CURRENT_DATE instead of CURRENT_TIMESTAMP (which is the standard SQL equivalent for the NOW function) because this will return results that are likely more correct

[Immerse]

The correcter, the better.

Thanks for your comments, interesting reading.
I'm not really planning on supporting anything other than MySQL in my app in the near future, but I always try to use as little proprietary syntaxes). I guess that I failed in this case

[swampBoogie]

The function current_timestamp, as defined in the sql standard, should include a time zone displacement. This means that the current_timetamp function in Mysql is not standard compliant.

There is a standard sql function named localtimestamp that does not include the time zone.

[SalientAnimal]

WOW, thanks to everyone on all your comments. It has definately helped me out a lot. I will keep all of this in mind when developing my site queries THanks guys