6.3.4 Date and Time Functions

This section describes the functions that can be used to manipulate temporal values. See section 6.2.2 Date and Time Types for a description of the range of values each date and time type has and the valid formats in which values may be specified.

Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

(Note that the query will also select records with dates that lie in the future.)

Functions that expect date values usually will accept datetime values and ignore the time part. Functions that expect time values usually will accept datetime values and ignore the date part.

The return value ranges in the following function descriptions apply for complete dates. If a date is a ``zero'' value or an incomplete date such as '2001-11-00', functions that extract a part of a date may return 0. For example, DAYOFMONTH('2001-11-00') returns 0.

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

DATE() is available as of MySQL 4.1.1.

TIME(expr)

Extracts the time part of the time or datetime expression expr.

mysql> SELECT TIME('2003-12-31 01:02:03');
        -> '01:02:03'
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
        -> '01:02:03.000123'

TIME() is available as of MySQL 4.1.1.

TIMESTAMP(expr)

TIMESTAMP(expr,expr2)

With one argument, returns the date or datetime expression expr as a datetime value. With two arguments, adds the time expression expr2 to the date or datetime expression expr and returns a datetime value.

mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'

TIMESTAMP() is available as of MySQL 4.1.1.

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard.

mysql> SELECT DAYOFWEEK('1998-02-03');
        -> 3

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
        -> 1
mysql> SELECT WEEKDAY('1997-11-05');
        -> 2

DAYOFMONTH(date)

Returns the day of the month for date, in the range 1 to 31:

mysql> SELECT DAYOFMONTH('1998-02-03');
        -> 3

DAY(date)

DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1.

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366:

mysql> SELECT DAYOFYEAR('1998-02-03');
        -> 34

MONTH(date)

Returns the month for date, in the range 1 to 12:

mysql> SELECT MONTH('1998-02-03');
        -> 2

DAYNAME(date)

Returns the name of the weekday for date:

mysql> SELECT DAYNAME('1998-02-05');
        -> 'Thursday'

MONTHNAME(date)

Returns the name of the month for date:

mysql> SELECT MONTHNAME('1998-02-05');
        -> 'February'

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4:

mysql> SELECT QUARTER('98-04-01');
        -> 2

WEEK(date)

WEEK(date,start)

With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range 0-53 or 1-52. The following table demonstrates how the start argument works:

Value	Meaning
0	Week starts on Sunday; return value range is 0 to 53
1	Week starts on Monday; return value range is 0 to 53
2	Week starts on Sunday; return value range is 1 to 53
3	Week starts on Monday; return value range is 1 to 53 (ISO 8601)

The start value of 3 can be used as of MySQL 4.0.5.

mysql> SELECT WEEK('1998-02-20');
        -> 7
mysql> SELECT WEEK('1998-02-20',0);
        -> 7
mysql> SELECT WEEK('1998-02-20',1);
        -> 8
mysql> SELECT WEEK('1998-12-31',1);
        -> 53

For MySQL 3.23 and 4.0, the default value for the start argument is 0. In MySQL 4.1, you can control the default value of the start argument by using the default_week_format variable. The syntax for setting default_week_format is:

SET [SESSION | GLOBAL] default_week_format = {0|1|2|3};

Note: in Version 4.0, WEEK(date,0) was changed to match the calendar in the USA. Before that, WEEK() was calculated incorrectly for dates in USA. (In effect, WEEK(date) and WEEK(date,0) was incorrect for all cases.) Note that if a date falls in the last week of the previous year, MySQL will return 0 if you don't use 2 or 3 as the optional start argument:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

One might argue that MySQL should return 52 for the WEEK() function, because the given date is actually the 52nd week of 1999. We decided to return 0 instead as we want the function to return ``the week number in the given year.'' This makes the usage of the WEEK() function reliable when combined with other functions that extract a date part from a date. If you would prefer to know the correct year-week, then you should use 2 or 3 as the optional start argument.

mysql> SELECT WEEK('2000-01-01',2);
        -> 52

Alternatively, use the YEARWEEK() function:

mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> '52'

WEEKOFYEAR(date)

Returns the calendar week of the date as a number in the range from 1 to 53.

mysql> SELECT WEEKOFYEAR('1998-02-20');
        -> 8

WEEKOFYEAR() is available as of MySQL 4.1.1.

YEAR(date)

Returns the year for date, in the range 1000 to 9999:

mysql> SELECT YEAR('98-02-03');
        -> 1998

YEARWEEK(date)

YEARWEEK(date,start)

Returns year and week for a date. The start argument works exactly like the start argument to WEEK(). Note that the year in the result may be different from the year in the date argument for the first and the last week of the year:

mysql> SELECT YEARWEEK('1987-01-01');
        -> 198653

Note that the week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year.

HOUR(time)

Returns the hour for time. The range of the return value will be 0 to 23 for time-of-day values:

mysql> SELECT HOUR('10:05:03');
        -> 10

However, the range of TIME values actually is much larger, so HOUR can return values greater than 23:

mysql> SELECT HOUR('272:59:59');
        -> 272

MINUTE(time)

Returns the minute for time, in the range 0 to 59:

mysql> SELECT MINUTE('98-02-03 10:05:03');
        -> 5

SECOND(time)

Returns the second for time, in the range 0 to 59:

mysql> SELECT SECOND('10:05:03');
        -> 3

MICROSECOND(expr)

Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.

mysql> SELECT MICROSECOND('12:00:00.123456');
        -> 123456
mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');
        -> 10

MICROSECOND() is available as of MySQL 4.1.1.

PERIOD_ADD(P,N)

Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value:

mysql> SELECT PERIOD_ADD(9801,2);
        -> 199803

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values:

mysql> SELECT PERIOD_DIFF(9802,199703);
        -> 11

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

These functions perform date arithmetic. As of MySQL Version 3.23, INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, it is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval. (See examples below.) date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is a keyword indicating how the expression should be interpreted. The following table shows how the type and expr arguments are related:

type Value	Expected expr Format
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
MONTH	MONTHS
YEAR	YEARS
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MICROSECOND	'MICROSECONDS'

The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise, the result is a DATETIME value:

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
        -> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
        -> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->                 INTERVAL '10000.999999' SECOND_MICROSECOND);
        -> '1993-01-01 02:46:40.000001'

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like '1:10', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day. Note that if you add or subtract a date value against something that contains a time part, the date value is automatically converted to a datetime value:

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
        -> '1999-01-02'
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
        -> '1999-01-01 01:00:00'

If you use really malformed dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD('1998-01-30', interval 1 month);
        -> '1998-02-28'

Note from the preceding example that the word INTERVAL and the type keyword are not case-sensitive.

ADDDATE(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

ADDDATE(expr,days)

SUBDATE(expr,days)

When invoked with the INTERVAL form of the second argument, ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
        -> '1998-02-02'
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
        -> '1998-02-02'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'

As of MySQL 4.1.1, the second syntax is allowed, where expr is a date or datetime expression and days is the number of days to be added to or subtracted from expr.

mysql> SELECT ADDDATE('1998-01-02', 31);
        -> '1998-02-02'
mysql> SELECT SUBDATE('1998-01-02', 31);
        -> '1997-12-02'

ADDTIME(expr,expr2)

SUBTIME(expr,expr2)

expr is a date or datetime expression, and expr2 is a time expression. [Rest of description to be added here]

NEED EXAMPLE

ADDTIME() and SUBTIME() were added in MySQL 4.1.1.

EXTRACT(type FROM date)

The EXTRACT() function uses the same kinds of interval type specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
       -> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
       -> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
       -> 20102
mysql> SELECT EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.00123");
        -> 123

DATEDIFF(ARGUMENTS)

TIMEDIFF(ARGUMENTS)

[Rest of description to be added here]

NEED EXAMPLE

DATEDIFF() and TIMEDIFF() were added in MySQL 4.1.1.

TO_DAYS(date)

Given a date date, returns a daynumber (the number of days since year 0):

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('1997-10-07');
        -> 729669

TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calendar was changed.

FROM_DAYS(N)

Given a daynumber N, returns a DATE value:

mysql> SELECT FROM_DAYS(729669);
        -> '1997-10-07'

FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calendar was changed.

DATE_FORMAT(date,format)

Formats the date value according to the format string. The following specifiers may be used in the format string:

Specifier	Description
%M	Month name (January..December)
%W	Weekday name (Sunday..Saturday)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)
%Y	Year, numeric, 4 digits
%y	Year, numeric, 2 digits
%X	Year for the week where Sunday is the first day of the week, numeric, 4 digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with %v
%a	Abbreviated weekday name (Sun..Sat)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%m	Month, numeric (00..12)
%c	Month, numeric (0..12)
%b	Abbreviated month name (Jan..Dec)
%j	Day of year (001..366)
%H	Hour (00..23)
%k	Hour (0..23)
%h	Hour (01..12)
%I	Hour (01..12)
%l	Hour (1..12)
%i	Minutes, numeric (00..59)
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%T	Time, 24-hour (hh:mm:ss)
%S	Seconds (00..59)
%s	Seconds (00..59)
%f	Microseconds (000000..999999)
%p	AM or PM
%w	Day of the week (0=Sunday..6=Saturday)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%%	A literal `%'.

All other characters are just copied to the result without interpretation. The %f format specifier is available as of MySQL 4.1.1. As of MySQL Version 3.23, the `%' character is required before format specifier characters. In earlier versions of MySQL, `%' was optional. The reason the ranges for the month and day specifiers begin with zero is that MySQL allows incomplete dates such as '2004-00-00' to be stored as of MySQL 3.23.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12:

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
        -> '100 100 04 04 4'

MAKEDATE(year,dayofyear)

Returns a date, given year and day-of-year values.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
        -> '2001-01-31', '2001-02-01'
mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
        -> '2001-12-31', '2004-12-30'

MAKEDATE() is available as of MySQL 4.1.1.

MAKETIME(hour,minute,second)

Returns a time value calculated from the hour, minute, and second arguments.

mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'

MAKETIME() is available as of MySQL 4.1.1.

CURDATE()

CURRENT_DATE

Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context:

mysql> SELECT CURDATE();
        -> '1997-12-15'
mysql> SELECT CURDATE() + 0;
        -> 19971215

CURTIME()

CURRENT_TIME

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026

NOW()

SYSDATE()

CURRENT_TIMESTAMP

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT NOW();
        -> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 19971215235026

Note that NOW() is evaluated only once per query at the start of query execution. This means that multiple references to NOW() within a single query will always give the same time.

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time:

mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit ``string-to-Unix-timestamp'' conversion. If you pass an out-of-range date to UNIX_TIMESTAMP() it returns 0, but please note that only basic checking is performed (year 1970-2037, month 01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP() columns, you may want to cast the result to signed integers. See section 6.3.5 Cast Functions.

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

If format is given, the result is formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function:

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2003 6th August 06:22:58 2003'

SEC_TO_TIME(seconds)

Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

TIME_TO_SEC(time)

Returns the time argument, converted to seconds:

mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

User Comments

When selecting a timestamp datatype from a table
and want adjust to a timezone (this example is
from pacific time to EST):
SELECT date_format(DATE_ADD([timestampcol],
INTERVAL 3 HOUR), '%M %e, %Y at %H:%i EST') FROM
[db table name];

<div class="comment">Does anyone else notice that
the function
YEARMONTH() is blatantly missing!!! Say you have
two dates and you want to do a period_diff...you
can't just concat() YEAR() and MONTH() because
month is not '0' padded...just seems with
YEARWEEK() it would make sense to also have
YEARMONTH()...thoughts? I know you can do it with
DATE_FORMAT, but then why all the other
functions?</div>

I have to wonder why there is no function that
does basically what this does:

<code>SELECT FLOOR((UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(date_of_birth))/60/60/24/364.25)
as age</code>

It would make a lot of code look a lot
cleaner.

This is even of particular importance for use
on
storing o collecting data about people in the US,
as US law prohibits collecting personal
information about anyone under 13 years of age,
and this trick figures out their age in years.

It would be a lot nicer with an AGE(date
[,date2]
[PERIOD]) function.

The closest thing in there is period_diff,
which
doesn't accept a standard date string and returns
months, which are &lt;sarcasm&gt;oh-so-
useful&lt;/sarcasm&gt;

Actually, this doesn't work in the case of pre-
epoch birthdates, worse yet. unix_timestamp
returns 0 for all pre-epoch dates.

I contend that that is a bug and really needs
to
be fixed.

Have to use period_diff/12 I guess.

You bring up some important issues, but
dealing with ages really isn't <em>that</em>
hard. For
example you could do something like this:

mysql> SELECT DATE_FORMAT(
FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0
AS age FROM people;

Where 'dob' is obviously their date of birth.
It'll also work with pre and post-epoch dates.
Please excuse the funky formatting as the
comment system seems to insist on inserting line
breaks into the code block. I
ran into this problem while working on some
genealogical things over at <a
href='http://www.mullenweg.com'>Mullenweg.com</a>
, a family site. I hope this helps!

<style>
div.comment {
border: 1px dashed black;
padding: 2px;
background-color: #F0F0F0;
}
</style>
<div class="comment">
Seems to be a real pain to get the days in the
month, but here is one way

<code>select
DATE_FORMAT(CONCAT(YEAR('2002-05-05'), '-',
MONTH('2002-05-05' + INTERVAL 1 MONTH), '-01') -
INTERVAL 1 DAY, '%e') as numDays</code>

I guess it would be nice if we could just have a
DATE_FORMAT entity for this</div>

If you're looking for generic SQL queries that will
allow you to get the days, months, and years
between any two given dates, you might consider
using these. You just need to substitute date1 and
date2 with your date fields and mytable with your
table name.


<table border="1">
<tr>
<td>
Number of days between date1 and date2:


SELECT TO_DAYS(date2) -
TO_DAYS(date1) FROM `mytable` WHERE
1</td>
</tr>
</table>


<table border="1">
<tr>
<td>
Number of months between date1 and date2:


SELECT PERIOD_DIFF
(DATE_FORMAT(date2,"%Y%m"),DATE_FORMAT
(date1,"%Y%m")) - (MID(date2, 6, 5) < MID(date1,
6, 5)) FROM `mytable` WHERE 1</td>
</tr>
</table>


<table border="1">
<tr>
<td>
Number of years between date1 and date2:


SELECT (YEAR(date2) - YEAR
(date1)) - (MID(date2, 6, 5) < MID(date1, 6, 5))
FROM `mytable` WHERE 1</td>
</tr>
</table>


Now for some comments about these.


1. These results return integer number of years,
months, and days. They are "floored." Thus, 1.4
days would display as 1 day, and 13.9 years would
display as 13 years.


2. Note that I use boolean expressions in some
cases. Because boolean expressions evaluate to 0
or 1, I can use them to subtract 1 from the total
based on a condition.


For example, to calculate the number of years
between to dates, first simply subtract the years.
The problem is that doing so isn't always correct.
Consider the number of years between July 1, 1950
and May 1, 1952. Technically, there is only one full
year between them. On July 1, 1952 and later,
there will be two years. Therefore, you should
subtract one year in case the date hasn't yet
reached a full year. This is done by checking the if
the second month-day is before the first month-
day. If so, this results in a value of 1, which is
subtracted from the total.


3. To get the month-day, I use MID. This is better
than using RIGHT, since it will work for both dates
and datetimes.


4. As mentioned in a previous post, PERIOD_DIFF
needs yearmonth format, but there is really no best
way to do this. To get this, I use DATE_FORMAT
(date1,"%Y%m").


5. Unlike many other solutions, these queries should
work with dates prior to 01/01/1970.


6. Feedback would be much appreciated. Since I'm
using this in my own applications, I would be happy if
you let me know if you discover an error in the logic.

A couple other time between functions. This is
another way to calculate the months between two
dates which may come in handy at times as it is
linear:
<div style="background-color: #eeeeee; border:
thin dashed black; margin: 5px; padding: 5px;">
Months Between 2002-02-15 and
2002-01-15
(((YEAR('2002-02-15') - 1) * 12 +
MONTH('2002-02-15')) - ((YEAR('2002-01-15') - 1) *
12 + MONTH('2002-01-15'))) - (MID('2002-01-15', 9,
2) < MID('2002-02-15', 9, 2))
</div>
The following is a weeks between function:
<div style="background-color: #eeeeee; border:
thin dashed black; margin: 5px; padding: 5px;">
Weeks Between 2002-08-28 and
2002-08-21
FLOOR((TO_DAYS('2002-08-28') -
TO_DAYS('2002-08-21')) / 7)
</div>

I have two datetime fields, (date_out, date_in) ,
they're records of loggin and logout times and I
need to find the way to get the difference between
the two of them, and I tried this one : f_out -
f_in but it gave me an integer result that is
worthless for me, I need the difference in
seconds Could you please help me , cause I don't
have an idea how to convert this answer to seconds

My user inputs a date in the format
of "MM/DD/YYYYY", how can I convert it to the
format "YYYY/MM/DD" before I save it on a mysql
table?

Hi All,
Adding to my friend Ricky Orea's query, If my user
enters the date in dd mmm yyyy format(26 nov
2002), how should i insert into the mysql db.

Thanks All

How to obtain number of days in a month :

just take the first day of the month, add one
month (to get the first day of the next month) and
substract one day (to get the last day of the
previous month, that is the number of days), as
follow :

select
dayofmonth(date_sub(date_add(concat(date_format(MYDATE,
'%Y-%m'), '-01'), interval 1 month), interval 1
day)) as number_of_days from MYTABLE;

(just replace MYDATE and MYTABLE)

perhaps there's an other way...

I'm wondering why there isn't an easy way to get
the next 5 birthdays out of a birthdaylist. so i always
have to do a 2nd select beginning at the neyt yeas's
first januar...

In order to get the number of seconds between two
datetime values in a table, you could use the
following: SELECT unix_timestamp(date1) -
unix_timestamp(date2) FROM table_name

I need to schedule weekly and monthly event, and my two questions are:
How can I find next Friday's date?
How can I find this month, second Wednesday's date?

Hi there,

I am trying to write a MySQL script that will populate a table with records for each value between 2 given parameters. Without the loop structue available in
Stored Procedures, this is proving problematic. Does anyone have a solution?

Rough Example:-
Table 'test' has a date field 'test_date'.

SELECT @TESTDATE=@START_DATE;
WHILE @TESTDATE <= @END_DATE
INSERT INTO test(test_date) values(@TESTDATE);
SELECT @TESTDATE=@START_DATE + INTERVAL 1 DAYS;
REPEAT

That is the general idea of what I want, not syntactically perfect, but you can see what I mean. I have thought of building a script file with the values I need and then running that, but I would still need to generate all values between the parameters.

Any ideas?

The following will query the dates in a database and give the second wednesday for that date if you set X, and Y to the following:-

X-Day of week to search for (0-monday, 6-Sunday), wednesday=2
Y-Number of weeks forward, from start of month. 1=first week of month, 2=second week of this month.

AvailDate is the field in my database containing a date value, change for your settings. eg change to "now()"

SELECT (AvailDate - INTERVAL (DAYOFMONTH(AvailDate)-1) DAY) + INTERVAL ((Y-1)*7) + ( MOD( ((X+7) - WEEKDAY((AvailDate)-INTERVAL(DAYOFMONTH(AvailDate)-1) DAY)), 7 ) ) DAY AS second_wednesday
FROM database

AND

Next friday can be done using the following, but this gives the same date if the date is a friday. (again X as previous, Friday=4)

SELECT AvailDate, (AvailDate + INTERVAL ( MOD( ((X+7) - WEEKDAY(AvailDate)), 7 ) ) DAY) AS next_friday FROM database

Hope this helps


Next 5 birthdays is easy

SELECT * FROM database
WHERE birthday > now()
ORDER BY birthday
LIMIT 5

ANF the age thing mentioned a few times :-
no-one seems to have fully read the listed functions, or it was added after they posted.(doubtfully)

EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03")
gives 199907 - the required format for PERIOD_DIFF

so age is
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM now()),EXTRACT(YEAR_MONTH FROM the_birth_date))/12

In reference to
"...I have two datetime fields,...loggin and logout times .....need to find the way to get the difference between the two of them..."

At first glance using sec_to_time(time_to_sec(out)-sec_to_time(in)) would work, but does not effectively calculate date. For login time '2002-12-20 23:25:17' and logout '2002-12-21 00:00:31':

select sec_to_time(time_to_sec(log.logout_time)-
time_to_sec(log.login_time)) from log where id=1;

-> -23:24:46 (whoops)

You can clearly see it is subtracting 00:00:31 time from 23:25:17, producing the negative number.

However using the unix timestamp, which gives us the number of seconds from '1970-01-01 00:00:00' for any case, we calculate the difference in seconds and turn it back into a time format:

select sec_to_time(unix_timestamp(log.logout_time)-
unix_timestamp(log.login_time)) from log where id=1;

-> 00:35:14

The result being, 'yippie,' we can go home sometime tonight.

Spent some time trying to work out how to calculate the month start x months ago ( so that I can create historical stats on the fly)

here is what I came up with..

((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1)

this gives you the first day of the month six months before the start of the current month in datetime format

Want your users' sessions to expire one hour after the datetime of their last activity? Create an "expiration time" alias from the last activity time using a datetime calculation and later compare it with the current time.

select *,
loginActivityTime + interval 60 minute as loginExpireTime
from loggedIn
where loginSessionID = '$sessionID'";

To get around the need for loops as in my previous posting, I suggest using PHP. This has solved my problem. PHP + MySQL = Prepare to Have Your Socks Blown Off!

Just a note for calculating age from the example above:

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM now()),EXTRACT(YEAR_MONTH FROM the_birth_date))/12

gives the age as 34.42 or whatever the actual age happens to be. Non-integer ages arent that useful. Yes, you can just floor it, but the point is there should be a more graceful solution to working with ages in mysql.

The Final Age Function (we hope)

The method posted by Mathew Mullenweg is good, but leap years goof it up on birthdays. (Try it. Use the current date and subtract exactly 5 years ago.)

Hopefully this will be the last 'find age' function. There is a simple premise to it:

1) Subtract the current year from the birth year to get the age.
2) If the current month and date is less than the birth month and date, subtract 1 from step 1.

Therefore, this should work with everyone who wasn't born in the future.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

where dob is date of birth.

In response to the fellow on Feb 20 having the problem with curdate() and he wants to add and subtract days here you go:

SELECT FROM_DAYS( TO_DAYS( curdate( ) ) + 90 )

would add 90 days to the current day. Just change the +90 to whatever, -7 to remove a week, etc.

It would also be nice to have a HOUR:SECOND where the HOUR is not limited in range. So if I was to SUM() a time column, I could get the total hours..

-- Christopher

why not add %Q and %q to DATE_FORMAT to get the quarter number? would be nice to say DATE_FORMAT(report_date,'Q%q %Y')

Not really that will just select 2003-02-01, 2003-03-31 for your dates...i dont know how you could do it to be honest..

Perhaps trying:

select * from table where datestamp > '2003-01-01' and datestamp < '2003-12-31'

dates must be in string context in this case.. Might not help with speed but if your date columns are indexed it may help.


C.

is there any function to find the difference of two dates

SELECT "2003-04-28" - INTERVAL 1 YEAR + INTERVAL 1 DAY
does not work; however:
SELECT "2003-04-28" + INTERVAL 1 DAY - INTERVAL 1 YEAR
works
SELECT ( "2003-04-28" - INTERVAL 1 YEAR ) + INTERVAL 1 DAY
also works

Parenthesize in any case for safety!

Here my solution for a birthday list. It shows the next birthdays but stops with the last day of the year.

select * from adress
where dayofyear(birthday) >= dayofyear(current_date)
order by dayofyear(birthday);

I know - it look bad - but it works :-)
A complete birthday list with the next comming birthdays round the whole and next year:

select * from adress
where
(sign(sign( dayofyear(birthday)-dayofyear(now())) -1)*(-366))+dayofyear(birthday) >= dayofyear(now())
order by
(sign(sign( dayofyear(birthday)-dayofyear(now())) -1)*(-366))+dayofyear(birthday);

Sorry - i'm again.
Here a more clear, better to understand solution for the Birthday list:

select * from adress
where
(( dayofyear(birthday) < dayofyear(now()) )*366)+dayofyear(birthday) >= dayofyear(now())
order by
(( dayofyear(birthday) < dayofyear(now()) )*366)+dayofyear(birthday)
;

Well I am not sure weather this is supposed to be so but I get strange results with the sec_to_time function on the windows version while I get different results on the linux side..

try this SELECT SEC_TO_TIME(97200) on linux I get 27:00:00

on Windows 23:00:00

looks like fr windows users time is always shorter.. which is a possibility of course :)

I found a problem during a test-run on a piece of code.

SELECT resetHH, HOUR(NOW()-resetHH) as difference, resetHH+INTERVAL 1 HOUR as resettoDT FROM table LIMIT 1

resetHH is a '2003-05-03 12:18:54' datetime stamp.
Sometimes I do get the hour difference between NOW and resetHH in the --> difference column. But sometimes it's just empty.

Poeple familiar with this? Am I making a mistake here?

if i wanted to select all rows on the table "notes" where the field "date" (of type datetime) is between the 1st of jan, 2003 and the 5th of may, 2003..how would i do it?

Mysql does not appear to have any grasp of timezones, and therefore no good way of dealing with them. For instance, if I want to know the unix_timestamp() for 2002-06-06 00:00:00 UTC, it's pretty hard.

If I know I live in a timezone without DST, it is possible to do: select unix_timestamp('2002-06-06 00:00:00') - unix_timestamp('1970-01-01 00:00:00')
However, that little trick only works for dates when DST is not in effect (i.e the winter months). I solved it this in this horrifying way:
select unix_timestamp('2002-06-06 00:00:00') - unix_timestamp('1970-01-01 00:00:00') + unix_timestamp(date_sub('2002-06-06 00:00:00', interval unix_timestamp('2002-06-06 00:00:00') second)) as utc_time;

It should be possible to do it somewhat simpler, but I can't figure it out right now. If you ask me though, the best solution wold be to make the unix_timestamp()-function parse timezones as well. If it was able to do so, the select above could be written as:
select unix_timestamp('2002-06-06 00:00:00 UTC');

While working on the above, I discovered another "missing" feature. Namely, the possibility to subtract a date from another and get an interval as a result.

(follow-up to that Posted by Twidi on December 18 2002)
There is another way...

How to obtain number of days in a month :
(i.e., the last day of the given month) :

SELECT to_days(curdate() + interval 1 month) - to_days(curdate())

Of course if you need something other than the current month [curdate()] use some other valid date, or date column.

Just a reminder: using functions like NOW(), or DATE_ADD() in your SQL does NOT require quotes around the function name! In fact, they will NOT work with quotes!

How can I find the date range that the week number returned by the week function relates to?

Regarding the timezone problem: if you need to know the current time in GMT, being safe with summer time, you can use

select date_sub(now(), interval time_to_sec(date_sub(now(),
interval unix_timestamp() second)) second) as utcnow

(thanks to Rasmus Tengblad for the idea)

Again regarding the timezone problem, the formula above doesn't seem to work west of Greenwich. Try instead:

select date_sub(now(), interval
time_to_sec(
date_sub(now(),interval unix_timestamp()-12*3600 second))-12*3600 second)

Dennis

To get the date difference between two date-type columns,
use this formula:

sec_to_time(unix_timestamp(EndDateTime) -
unix_timestamp(StartDateTime))

where StartDateTime and EndDateTime are the two columns

/A

I have read a lot of the birthday issues....
I think I found the most easy way to determine if someone's birthday is in the next X days:

select * from YOUR_TABLE where DAYOFYEAR(BIRTHDAY) BETWEEN DAYOFYEAR(CURRENT_DATE) and (DAYOFYEAR(CURRENT_DATE) + 30);

In this case, everyone who's birthday is in the next 30 days, will be displayed... Change the number 30 to anything you'd like of course....

Several times i have come to a followng date/time problem:
In the table i am storing both date and time information in the datetime column. Querying, I want to receive COUNTed results grouped by date, and not date and time. I came to the easy solution:
SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM MyTable GROUP BY dd;

I suppose this solution to be quite slow (date formatting).

Later, i 'upgraded' this query to use the string function:
SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

knowing, that the result is in the fixed format. Works faster.

Add your own comment.