MySQL Time Date Function example

Last Update:2017-01-13 Source: Internet

Author: User

Tags current time datetime month name

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

1.DAYOFWEEK (date), which returns the data time of the week (1 => Sunday, 2 => Monday, 3 => Tuesday, ..., 7 => Saturday).

Mysql> Select DayOfWeek (' 2013-06-09 14:00:00 ');
+----------------------------------+
| DayOfWeek (' 2013-06-09 14:00:00 ') |
+----------------------------------+
| 1 |
+----------------------------------+
2.DAYOFMONTH (date); Returns date is the day ordinal of one months 1--31

Mysql> Select DayOfMonth (' 2013-06-09 14:00:00 ');
+-----------------------------------+
| DayOfMonth (' 2013-06-09 14:00:00 ') |
+-----------------------------------+
| 9 |
+-----------------------------------+
3.DAYOFYEAR (date); Returns date is the day of the Year 1 ——— 366

Mysql> Select DayOfYear (' 2013-06-09 14:00:00 ');
+----------------------------------+
| DayOfYear (' 2013-06-09 14:00:00 ') |
+----------------------------------+
|160 |
+----------------------------------+
4.WEEKDAY (date); Returns date is the week (0 => Monday, 1 => Tuesday, 2 => Wednesday, ..., 6=> Sunday).

Mysql> Select Weekday (' 2013-06-09 14:00:00 ');
+--------------------------------+
| Weekday (' 2013-06-09 14:00:00 ') |
+--------------------------------+
|6 |
+--------------------------------+
5.MONTH (date); Returns the month 1-–12 of the date

Mysql> Select MONTH (' 2013-06-09 14:00:00 ');
+------------------------------+
| MONTH (' 2013-06-09 14:00:00 ') |
+------------------------------+
| 6 |
+------------------------------+
6.DAYNAME (date) returns the weekday English name of the day

Mysql> Select Dayname (' 2013-06-09 14:00:00 ');
+--------------------------------+
| Dayname (' 2013-06-09 14:00:00 ') |
+--------------------------------+
| sunday|
+--------------------------------+
7.MONTHNAME (date); Returns the English name of the month of date

Mysql> Select MonthName (' 2013-06-09 14:00:00 ');
+----------------------------------+
| MonthName (' 2013-06-09 14:00:00 ') |
+----------------------------------+
| June |
+----------------------------------+
8.QUARTER (date) Returns the sort of date in the quarter 1 ——-4

Mysql> Select quarter (' 2013-06-09 14:00:00 ');
+--------------------------------+
| Quarter (' 2013-06-09 14:00:00 ') |
+--------------------------------+
|
+--------------------------------+
9.WEEK (Date,first); Returns date is the week ordinal of a year, first = 0 starting from Sunday, and first = 1 starting from Monday defaults to 00--–52

Mysql> Select WEEK (' 2013-06-09 14:00:00 ', 0);
+-------------------------------+
| WEEK (' 2013-06-09 14:00:00 ', 0) |
+-------------------------------+
| 23 |
+-------------------------------+
10.HOUR (date); Returns the hour portion of date

Mysql> Select HOUR (' 2013-06-09 14:00:00 ');
+-----------------------------+
| HOUR (' 2013-06-09 14:00:00 ') |
+-----------------------------+
| 14 |
+-----------------------------+
11.YEAR (date); Returns the year part of date

Mysql> Select year (' 2013-06-09 14:00:00 ');
+-----------------------------+
| Year (' 2013-06-09 14:00:00 ') |
+-----------------------------+
| 2013 |
+-----------------------------+
12.MINUTE (date); Returns the minute portion of date

Mysql> Select MINUTE (' 2013-06-09 14:22:22 ');
+-------------------------------+
| MINUTE (' 2013-06-09 14:22:22 ') |
+-------------------------------+
| 22 |
+-------------------------------+
13.SECOND (date); Returns the second part of date

Mysql> Select SECOND (' 2013-06-09 14:22:22 ');
+-------------------------------+
| SECOND (' 2013-06-09 14:22:22 ') |
+-------------------------------+
| 22 |
+-------------------------------+
14.period_add (date,num);d ate plus num Date date format is YYYYMMDD or YYYYMM, if accurate to the day NUM Unit is the day, if the month NUM is the month unit increase

Mysql> Select Period_add (201306,3);
+----------------------+
| Period_add (201306,3) |
+----------------------+
|201309 |
+----------------------+
Mysql> Select Period_add (20130609,3);
+------------------------+
| Period_add (20130609,3) |
+------------------------+
|20130612 |
+------------------------+
15.period_diff (date1,date2);d ate1 minus date2 number of difference months

Mysql> Select Period_diff (201306,201309);
+----------------------------+
| Period_diff (201306,201309) |
+----------------------------+
|-3 |
+----------------------------+
16.date_add (date, INTERVAL num type) = = Adddate (date, INTERVAL num type);D ate_sub (date, INTERVAL num type) = = Subdate (date , INTERVAL num type); date adds or subtracts a set unit time

Value of type: second,minute,hour,day,month,week,month,year

Mysql> Select Date_add (' 2013-06-09 14:22:22 ', INTERVAL 1 day);
+------------------------------------------------+
| Date_add (' 2013-06-09 14:22:22 ', INTERVAL 1 day) |
+------------------------------------------------+
| 2013-06-10 14:22:22 |
+------------------------------------------------+
Mysql> Select Adddate (' 2013-06-09 14:22:22 ', INTERVAL 1 day);
+-----------------------------------------------+
| Adddate (' 2013-06-09 14:22:22 ', INTERVAL 1 day) |
+-----------------------------------------------+
| 2013-06-10 14:22:22 |
+-----------------------------------------------+
16.to_days (date), given a date to return a number of days (starting from 0 years)

Mysql> Select To_days (' 2013-06-09 14:22:22 ');
+--------------------------------+
| To_days (' 2013-06-09 14:22:22 ') |
+--------------------------------+
|735393 |
+--------------------------------+
17.from_days (num); Returns a date for a given number of days

Mysql> Select From_days (752341);
+-------------------+
| From_days (752341) |
+-------------------+
| 2059-11-03 |
+-------------------+
18.date_format (Date,format); Format Date

% W Week name (Sunday ...) Saturday)
%d The date of the month with the English suffix (1st, 2nd, 3rd, and so on).
%Y years, numbers, 4-bit
%Y years, numbers, 2-bit
%a abbreviated week names (Sun ...). Sat)
days in%d months, numbers (00 ...).
%e The number of days in the month, numbers (0 ...).
%m Month, number (01 ...).
%c Month, number (1 ...).
%b The abbreviated month name (...). DEC)
%j The number of days in a year (001 ...). 366)
%H hours (00 ...).
%k hours (0 ...).
%h hours (01 ...). )
%I Hours (01 .....)
%l hours (1 ...).
%i minutes, number (00 ...). (%r)
Time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ...).
%s seconds (00 ...).
%p am or PM
%w the number of days in one weeks (0=sunday ...). 6=saturday)
%u weeks (0 ...). 52), here Sunday is the first day of the week
%u weeks (0 ...). 52, here Monday is the first day of the week
percent% of a text.
Mysql> Select Date_format (' 2013-06-09 14:22:22 ', '%y-%m-%d ');
+-----------------------------------------------+
| Date_format (' 2013-06-09 14:22:22 ', '%y-%m-%d ') |
+-----------------------------------------------+
| 2013-06-09 |
+------------------------------------- ----------+
19.CURDATE () = = Current_date (): Digital return and string return

Mysql> select Curdate ();
+------------+
| Curdate () |
+------------+
| 2013-08-22 |
+------------+
Mysql> Select curdate () + 0;
+---------------+
| Curdate () + 0 |
+---------------+
| 20130822 |
+---------------+
Mysql> Select Curdate () + 1;
+---------------+
| Curdate () + 1 |
+---------------+
| 20130823 |
+---------------+
20.CURTIME () = = Current_time (); Divided into digital return and string return

Mysql> select Curtime ();
+-----------+
| Curtime () |
+-----------+
| 14:08:37 |
+-----------+
Mysql> Select Curtime () + 0.;
+----------------+
| Curtime () + 0. |
+----------------+
| 140841.000000 |
+----------------+
21.NOW (), Unix_timestamp (); Time and time stamp of the present moment

Mysql> SELECT now ();
+---------------------+
| Now () |
+---------------------+
| 2013-08-22 14:13:59 |
+---------------------+
Mysql> SELECT Unix_timestamp ();
+------------------+
| Unix_timestamp () |
+------------------+
| 1377152057 |
+------------------+
22.from_unixtime (Unix); Converting a Unix timestamp to a date

Mysql> Select From_unixtime (1377152057);
+---------------------------+
| From_unixtime (1377152057) |
+---------------------------+
| 2013-08-22 14:14:17 |
+---------------------------+

Add

--Returns the current time

Mysql> Select Curdate (), Curtime (), now (), DATE (now ()), sysdate ();

+------------+-----------+---------------------+-------------+---------------------+

| Curdate () | Curtime () | Now () | DATE (Now ()) | Sysdate () |

+------------+-----------+---------------------+-------------+---------------------+

| 2008-12-02 | 10:11:36 | 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |

+------------+-----------+---------------------+-------------+---------------------+

1 row in Set (0.00 sec)

Mysql> SELECT curdate (), Curdate () +0,curtime (), Curtime () +0;

+------------+-------------+-----------+---------------+

| Curdate () | Curdate () +0 | Curtime () | Curtime () +0 |

+------------+-------------+-----------+---------------+

|    2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |

+------------+-------------+-----------+---------------+

1 row in Set (0.00 sec)

--Return date last day of month

Mysql> Select Last_day (' 2008-12-02 ');

+------------------------+

| Last_day (' 2008-12-02 ') |

+------------------------+

| 2008-12-31 |

+------------------------+

1 row in Set (0.00 sec)

--Day of the week of the return date

Mysql> Select Dayname (' 2008-12-02 '), DayOfWeek (' 2008-12-02 ');

+-----------------------+-------------------------+

| Dayname (' 2008-12-02 ') | DayOfWeek (' 2008-12-02 ') |

+-----------------------+-------------------------+

|                       Tuesday | 3 |

+-----------------------+-------------------------+

1 row in Set (0.00 sec)

--return date of year, month, day

Mysql> Select month (' 2008-12-02 '), year (' 2008-12-02 '), Day (' 2008-12-02 ');

+---------------------+--------------------+-------------------+

| Month (' 2008-12-02 ') | Year (' 2008-12-02 ') | Day (' 2008-12-02 ') |

+---------------------+--------------------+-------------------+

|               12 |                 2008 | 2 |

+---------------------+--------------------+-------------------+

1 row in Set (0.00 sec)

--Returns the hour of the date, minutes, seconds

mysql> SELECT HOUR (' 10:05:03 '), MINUTE (' 10:05:03 '), SECOND (' 10:05:03 ');

+------------------+--------------------+--------------------+

| HOUR (' 10:05:03 ') | MINUTE (' 10:05:03 ') | SECOND (' 10:05:03 ') |

+------------------+--------------------+--------------------+

|                  10 |                  5 | 3 |

+------------------+--------------------+--------------------+

1 row in Set (0.00 sec)

1.SUBDATE (d,t): Start time plus some time

--Return start time plus n days

mysql> SELECT date_add (' 1998-01-02 ', INTERVAL Day), Adddate (' 1998-01-02 ', 31);

+-----------------------------------------+---------------------------+

| Date_add (' 1998-01-02 ', INTERVAL day) | Adddate (' 1998-01-02 ', 31) |

+-----------------------------------------+---------------------------+

| 1998-02-02 | 1998-02-02 |

+-----------------------------------------+---------------------------+

1 row in Set (0.00 sec)

--Return start time plus year, month

mysql> SELECT date_add (' 1998-01-02 ', INTERVAL 2 year), Date_add (' 1998-01-02 ', INTERVAL 2 MONTH);

+-----------------------------------------+------------------------------------------+

| Date_add (' 1998-01-02 ', INTERVAL 2 year) | Date_add (' 1998-01-02 ', INTERVAL 2 MONTH) |

+-----------------------------------------+------------------------------------------+

| 2000-01-02 | 1998-03-02 |

+-----------------------------------------+------------------------------------------+

1 row in Set (0.00 sec)

--Return start time plus hours, plus minutes

mysql> SELECT date_add (' 1998-01-02 ', INTERVAL 2 hour), Date_add (' 1998-01-02 ', INTERVAL 2 minute);

+-----------------------------------------+-------------------------------------------+

| Date_add (' 1998-01-02 ', INTERVAL 2 hour) | Date_add (' 1998-01-02 ', INTERVAL 2 minute) |

+-----------------------------------------+-------------------------------------------+

| 1998-01-02 02:00:00 | 1998-01-02 00:02:00 |

+-----------------------------------------+-------------------------------------------+

1 row in Set (0.00 sec)

2.SUBDATE (d,t): Start time minus a period of time

mysql> SELECT subdate (' 1998-01-02 ', INTERVAL Day), Subdate (' 1998-01-02 ', 31);

+----------------------------------------+---------------------------+

| Subdate (' 1998-01-02 ', INTERVAL day) | Subdate (' 1998-01-02 ', 31) |

+----------------------------------------+---------------------------+

| 1997-12-02 | 1997-12-02 |

+----------------------------------------+---------------------------+

1 row in Set (0.00 sec)

3.ADDTIME (d,t): Start time D join time t

mysql> SELECT addtime (' 1997-12-31 23:59:50 ', ' 00:00:05 '), Addtime (' 23:59:50 ', ' 00:00:05 ');

+-------------------------------------------+--------------------------------+

| Addtime (' 1997-12-31 23:59:50 ', ' 00:00:05 ') | Addtime (' 23:59:50 ', ' 00:00:05 ') |

+-------------------------------------------+--------------------------------+

| 1997-12-31 23:59:55 | 23:59:55 |

+-------------------------------------------+--------------------------------+

1 row in Set (0.00 sec)

4.SUBTIME (d,t): Starting time D minus time t

mysql> SELECT subtime (' 1997-12-31 23:59:50 ', ' 00:00:05 '), Subtime (' 23:59:50 ', ' 00:00:05 ');

+-------------------------------------------+--------------------------------+

| Subtime (' 1997-12-31 23:59:50 ', ' 00:00:05 ') | Subtime (' 23:59:50 ', ' 00:00:05 ') |

+-------------------------------------------+--------------------------------+

| 1997-12-31 23:59:45 | 23:59:45 |

+-------------------------------------------+--------------------------------+

1 row in Set (0.00 sec)

5.DATEDIFF (D1,D2): Returns the number of days between the start time D1 and the end time D2

mysql> SELECT DATEDIFF (' 1997-12-31 23:59:59 ', ' 1997-12-30 ');

+----------------------------------------------+

| DATEDIFF (' 1997-12-31 23:59:59 ', ' 1997-12-30 ') |

+----------------------------------------------+

| 1 |

+----------------------------------------------+

1 row in Set (0.00 sec)

6.date_format (Date,format): Displays the format of the DATE value based on the format string

mysql> SELECT date_format (' 2008-12-02 22:23:00,%Y%m%m%h:%i:%s ');

+---------------------------------------------------------+

| Date_format (' 2008-12-02 22:23:00, '%Y%m%m%h:%i:%s ') |

+---------------------------------------------------------+

| 2008 12 12 22:23:00 |

+---------------------------------------------------------+

1 row in Set (0.00 sec)

7.str_to_date (Str,format) string converted to time

mysql> SELECT str_to_date (' 04/31/2004 ', '%m/%d/%y%h:%i:s ');

+-----------------------------------------------+

| Str_to_date (' 04/31/2004 ', '%m/%d/%y%h:%i:s ') |

+-----------------------------------------------+

| 2004-04-31 00:00:00 |

+-----------------------------------------------+

1 row in Set (0.00 sec)

8.TIMESTAMP (expr), TIMESTAMP (EXPR,EXPR2):

For a single argument, the function returns the date or datetime expression expr as a DateTime value. For two parameters, it adds the time expression expr2 to the date or datetime expression expr, returning Theresult as a datetime value

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

+-------------------------+---------------------------------------------+

| TIMESTAMP (' 2003-12-31 ') | TIMESTAMP (' 2003-12-31 12:00:00 ', ' 12:00:00 ') |

+-------------------------+---------------------------------------------+

| 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |

+-------------------------+---------------------------------------------+

1 row in Set (0.00 sec)

-0:0, next day 0:0
Mysql> Select timestamp (date (sysdate ()), timestamp (Adddate (Sysdate ()), 1);
+----------------------------+---------------------------------------+
| Timestamp (date (sysdate ()) | Timestamp (adddate date (Sysdate ()), 1) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in Set (0.00 sec)

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More