updated 10-28-2019
The CURDATE(), CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME()
functions return values in the connection’s current time zone, which is available as the value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the current time zone.
The output of CURRENT_DATE has the format: YYYY-MM-DD
The DATE_FORMAT() function allows you to return a date in a specified format
example:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') ;
result: ‘Sunday October 2009’
the current date and time are evaluated only at the start of the query
A.K.A.: Stay constant throughout the query.
Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), and to any of their synonyms.
“The proper format of a DATE is: YYYY-MM-DD. If you try to enter a date in a format other than the Year-Month-Day format, it might work but it won’t be storing the dates as you expect.”
In order to run a MySQL Insert command and add the current date into your table you can use MySQL’s built-in function CURDATE() in your query.
Unix Time
UNIX TIME: the number of seconds since the first second of the first day of 1970.
example: $start = ‘1234567890’ ;
FROM_UNIXTIME($start) # result: 2009-02-13 21:31:30
FROM_UNIXTIME(0) # result: 1969-12-31 19:00:00 EST = 1970-01-01 00:00:00 UTC
UNIX_TIMESTAMP(‘2009-02-13 21:31:30’) # result: 1234567890
$start = '1234567890' ;
mysql # result
---------------------------------------------- -------------------
SELECT FROM_UNIXTIME($start) # 2009-02-13 16:31:30
SELECT UNIX_TIMESTAMP('2009-02-13 16:31:30') # 1234567890
SELECT substring(FROM_UNIXTIME($start), 1, 10) # 2009-02-13
display a unix timestamped order date
SELECT o.orderid, IF(o.unixtime = 0, 0, FROM_UNIXTIME(o.unixtime) )
FROM orders AS o
For example, to delete time-stamped records older than 10 minutes:
DELETE FROM events
WHERE timestamp < (NOW() - INTERVAL 10 MINUTE)
Or, for deleting records that are over 90 days old:
DELETE FROM events
WHERE timestamp < (NOW() - INTERVAL 90 DAY)
Or, for deleting records that are in the UNIX time format, (ex: timecreated = 1234567890)
DELETE FROM log_table
WHERE timecreated <
UNIX_TIMESTAMP(CURDATE() - INTERVAL 90 DAY ) ;
Display subtotals by date out of a timestamp field (date_purchased)
SELECT substring(date_purchased, 1, 10) AS saledate,
SUM(order_total) AS subttl
FROM orders
WHERE date_purchased > DATE_SUB( NOW(), INTERVAL 180 DAY )
GROUP BY saledate
HAVING subttl > 3500
ORDER BY subttl DESC
note: "DATE_SUB()" is not necessary
then take the sub-totals by day and display the Average sub-total over a month range . . .
like daily subtotals averaged each month - (sorted by date {salemonth} or by sub-total {avettl})
SELECT substring(date_purchased, 1, 7) AS salemonth,
SUM(order_total) /
COUNT(DISTINCT substring(date_purchased, 1, 10)) AS avettl
FROM orders
WHERE date_purchased > NOW() - INTERVAL 5 YEAR
GROUP BY salemonth
ORDER BY salemonth DESC
Display weekly subtotals out of a timestamp field; (Mon --> Sun)
WEEKOFYEAR() generates a number: 1-53 ex:
SELECT WEEKOFYEAR(substring(date_purchased, 1, 10)) AS saleweek, If you group by WEEKOFYEAR() and display the date purchased (saledate),
substring(date_purchased, 1, 10) AS saledate,
SUM(order_total) AS subttl
FROM orders
WHERE date_purchased > NOW() - INTERVAL 1 YEAR
GROUP BY saleweek
ORDER BY saleweek DESC
you will get the Monday, the first day of the week.
SELECT UNIX_TIMESTAMP('2010-01-01'); # 1262322000
SELECT UNIX_TIMESTAMP('2011-01-01') - UNIX_TIMESTAMP('2010-01-01') AS 1year ; # 31536000
SELECT UNIX_TIMESTAMP('2012-01-15') - UNIX_TIMESTAMP('2012-01-14') AS 1day ; # 86400
86400 seconds = 1 day
3600 seconds = 1 hour
NOW()
DATE_FORMAT( NOW(), '%m-%d-%Y' ) ex: 02-05-2016
DATE_FORMAT( NOW(), '%b %d %Y %h:%i %p' ) ex: Feb 05 2016 10:35 PM
The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:DD' or 'YYYYMMDDHHMMSS.uuuuuu' format.
-- mysql now minus 1 hour
SELECT (NOW() - INTERVAL 1 HOUR) AS 'NOW - 1 hour',
NOW(),
-- mysql now plus 1 hour
NOW() + INTERVAL 1 HOUR AS 'NOW + 1 hour';
-- mysql now minus 1 day
SELECT (NOW() - INTERVAL 1 DAY) AS 'NOW - 1 day',
NOW(),
-- mysql now plus 1 day
(NOW() + INTERVAL 1 DAY) AS 'NOW + 1 day';
column in a table:
created DATETIME NOT NULL DEFAULT NOW()
converting seconds to days, hours, minutes, and seconds
example: to display the difference between 2 ("unix") times as days, hrs, min, and sec
CONCAT(
IF((endtime - starttime) >= 86400,
CONCAT( (endtime - starttime) DIV 86400, ' days '), ' '),
IF( MOD(endtime - starttime, 86400) >= 3600,
CONCAT( MOD(endtime - starttime, 86400) DIV 3600, ' hrs '), ' '),
IF( MOD(endtime - starttime, 3600) >= 60,
CONCAT( MOD(endtime - starttime, 3600) DIV 60, ' min ' ), ' '),
IF( MOD(endtime - starttime, 60) > 0 ,
CONCAT( MOD(endtime - starttime, 60), ' sec '), ' ')
) AS timediff,
CASTing Strings to Dates and Strings to Intergers (unsigned)
1.) cast the years as intergers: the year substringed out of the timestamp on the database record (example: 2011-05-05 22:38:19) and compared to a variable
example: $startdate = '01-24-2011' ;
SELECT *, substring(ts, 1, 10) AS time_stamp
FROM user
WHERE confirmed=1 AND deleted=0
AND CAST(substring(ts, 1, 4) AS UNSIGNED ) >=
CAST(substring('$startdate', 7, 4) AS UNSIGNED )
ORDER BY lastname, firstname
2.) cast the string to a date in the format yyyy-mm-dd - - first, turn around the Startdate (example: 01-25-2011)
$dtarr = explode("-", $Startdate);
$StSqldate = date("Y-m-d", strtotime($dtarr[2].'-'.$dtarr[0].'-'.$dtarr[1]) );
SELECT id, username, lastname, firstname, substring(ts, 1, 10) AS time_stamp
FROM user
WHERE confirmed=1 AND deleted=0
AND CAST(substring(ts, 1, 10) AS DATE ) >= CAST('$StSqldate' AS DATE )
ORDER BY lastname, firstname
Date - Time Arithmetic
example of displaying a difference in 2 time stamps in hours, minutes, & seconds:
SELECT starttime, completed,
FROM_UNIXTIME(starttime) AS starttimeYmdHms,
FROM_UNIXTIME(completed) AS completedYmdHms,
(completed - starttime) AS secs,
CONCAT(
IF((g.completed - t.starttime) >= 3600,
CONCAT( (completed - starttime) DIV 3600, ' hrs '), ' '),
IF( MOD(completed - starttime, 3600) >= 60,
CONCAT( MOD(completed - starttime, 3600) DIV 60, ' min ' ), ' '),
IF( MOD(completed - starttime, 60) > 0 ,
CONCAT(MOD(completed - starttime, 60), ' sec '), ' ')
) AS hrs-min-secs
EXAMPLE OUTPUT: starttime completed starttimeYmdHms completedYmdHms secs hrs-min-secs 1222949590 1222949774 2008-10-02 06:13:10 2008-10-02 06:16:14 184 3 min 4 sec 1223036960 1223039059 2008-10-03 06:29:20 2008-10-03 07:04:19 2099 34 min 59 sec 1223039077 1223039656 2008-10-03 07:04:37 2008-10-03 07:14:16 579 9 min 39 sec 1223154411 1223159710 2008-10-04 15:06:51 2008-10-04 16:35:10 5299 1 hrs 28 min 19 sec 1223159743 1223160821 2008-10-04 16:35:43 2008-10-04 16:53:41 1078 17 min 58 sec 1223160835 1223169670 2008-10-04 16:53:55 2008-10-04 19:21:10 8835 2 hrs 27 min 15 sec 1223169751 1223169910 2008-10-04 19:22:31 2008-10-04 19:25:10 159 2 min 39 sec 1223170451 1223170558 2008-10-04 19:34:11 2008-10-04 19:35:58 107 1 min 47 sec 1223170587 1223170646 2008-10-04 19:36:27 2008-10-04 19:37:26 59 59 sec 1223170652 1223171150 2008-10-04 19:37:32 2008-10-04 19:45:50 498 8 min 18 sec 1223172315 1223172500 2008-10-04 20:05:15 2008-10-04 20:08:20 185 3 min 5 sec 1223173091 1223173177 2008-10-04 20:18:11 2008-10-04 20:19:37 86 1 min 26 sec 1223173260 1223173283 2008-10-04 20:21:00 2008-10-04 20:21:23 23 23 sec