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
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.
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
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
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
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
Check out the MySQL manual's Date and Time Functions page for lots of useful stuff:
Basically, use DATE_SUB and intervals
SELECT * FROM table WHERE date > DATE_SUB(CURRENT_TIMESTAMP, interval 7 day)
i'd use CURRENT_DATE instead of CURRENT_TIMESTAMP in this scenario, dan