SQL Syntax Question

Hi all,

I am using the following to get the database values in the past 24 hours.

timestampadd(hour, -24, now()

Could someone tell me what to change in this so I can get them for the past week, the past month, and the past year?

Thank you so much,

D
TLN_CANADAAsked:
Who is Participating?
 
Tony303Connect With a Mentor Commented:
OK,

2 things.

1.
Perhaps we cannot use WW for week, MM for Month and YY for Year?
Following on from your example that works...

timestampadd(Week, -1, now() for week
timestampadd(Month, -1, now() for month
timestampadd(Year, -1, now() for year

2.
Writing pure SQL would also not have now() for current date time.
It would be GETDATE().

But since your "daily" syntax works using hour, -24 , Now(). I would be loath to change it.

T
0
 
Tony303Commented:
Does this work?

timestampadd(ww, -1, now() for week?
timestampadd(MM, -1, now() for month?
timestampadd(YY, -1, now() for year?
0
 
TLN_CANADAAuthor Commented:
I don't think so,

here is the line:

$query12 = mysql_query("SELECT * FROM exercising_table WHERE exercise_timestamp  > timestampadd(ww, -1, now())");
	$total_exercises_today2 = mysql_num_rows($query12);

Open in new window


and the error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/clear555/public_html/dashboardstats.php on line 51
0
 
Tony303Commented:
Sorry, NOW() is the valid date for current date time in My SQL.
Forget #2 in my previous post.


See...http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

TIMESTAMPADD(unit,interval,datetime_expr)

 Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

It is possible to use FRAC_SECOND in place of MICROSECOND, but FRAC_SECOND is deprecated. FRAC_SECOND was removed in MySQL 5.5.3.

The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
        -> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
        -> '2003-01-09'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.