MySQL Date Queries

Databases
1

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

2

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.

3

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 :slight_smile: THanks guys

4

The correcter, the better. :slight_smile:

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 :wink:

5

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 :slight_smile:

6

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

:slight_smile:

7

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

Basically, use DATE_SUB and intervals

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

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

:slight_smile: