MySQL Common date and time function detailed

MySQL Date Type: Date format, occupied storage space, date range comparison.


Date type storage space Date format date range


------------  ---------   --------------------- -----------------------------------------


DateTime 8 bytes Yyyy-mm-dd HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59


Timestamp 4 bytes Yyyy-mm-dd HH:MM:SS 1970-01-01 00:00:01 ~ 2038


Date 3 bytes Yyyy-mm-dd 1000-01-01 ~ 9999-12-31


Year 1 bytes YYYY 1901 ~ 2155

The now () function gets the current date and time:

The code is as follows	Copy Code
Select Now (); /* +---------------------+ | Now () | +---------------------+ | 2009-05-18 20:11:24 | +---------------------+ 1 row in Set (0.02 sec) */

Well, this function is often used by me, it's OK to look at the current time. See how long it is before work, hehe.

Curtime () and curdate () are more specific than now (), returning the current time and date:

The code is as follows	Copy Code
Select Curtime (), curdate (); /* +-----------+------------+ | Curtime () | Curdate () | +-----------+------------+ | 20:13:28 | 2009-05-18 | +-----------+------------+ 1 row in Set (0.02 sec) */

The Unix_timestamp () function returns the UNIX timestamp:

The code is as follows	Copy Code
Select Unix_timestamp (); /* +------------------+ | Unix_timestamp () | +------------------+ | 1242648913 | +------------------+ 1 row in Set (0.00 sec) */

Well, how does the timestamp change to the time? Are you sure you want to use PHP's Strtotime ()? Oh, that's too much trouble, huh? Don't worry, it's coming.

The From_unixtime () function converts the Unix timestamp to the date and time when MySQL is readable:

The code is as follows	Copy Code
Select From_unixtime (Unix_timestamp ()); /* +---------------------------------+ | From_unixtime (Unix_timestamp ()) | +---------------------------------+ | 2009-05-18 20:17:23 | +---------------------------------+ 1 row in Set (0.02 sec) */

I feel this example is easier for you to understand than reading a from_unixtime directly (), isn't it?

Year ()--years, MONTH (), MonthName ()--month, DayOfYear (), DayOfWeek (), DayOfMonth ()--the ordinal number of the day in the year, Week, and month:

code is as follows

copy code

Select year (20030415012345), year (2009-05-18), /* +------------- ---------+------------------+ | 20030415012345 year (2009-05-18) | +----------------------+------------------+ | 2003 | NULL | +----------------------+------------------+ 1 row in set, 1 Warning (0.02 sec) */ Select year (20030415012 345), year (' 2009-05-18 '); /* +----------------------+--------------------+ | year (20030415012345) | (' 2009-05-18 ') | +----- -----------------+--------------------+ | 2003 | 2009 | +----------------------+--------------------+ 1 row in Set (0.02 sec) */ ,

Did you see that? If I don't enclose the formatted date in quotes, the return is null.

Also, if year () has no parameters, there will be an error. Well. I've just had a trial.

code is as follows

copy code

select month ( 20030414012345), month (' 2009-05-18 '), month (2009-05-18), month (' 2009-13-18 '); /* +-----------------------+---------------------+-------------------+---------------------+ | month ( 20030414012345) | Month (' 2009-05-18 ') | Month (2009-05-18) | Month (' 2009-13-18 ') | +-----------------------+---------------------+-------------------+---------------------+ | 4 | 5 | NULL | NULL | +-----------------------+---------------------+-------------------+---------------------+ 1 row in set, 2 Warnings (0.09 sec) */

Did you see that? Similarly, if the month is not legal, it will return null, HA, and the formatted parameter must be enclosed in quotation marks.

The code is as follows

Copy Code

Select MonthName (' 20090518 '), MonthName (' 2009-05-18 '), MonthName (2009-05-18), MonthName (' 2009-13-18 '); /* +-----------------------+-------------------------+-----------------------+-------------------------+ | MonthName (' 20090518 ') | MonthName (' 2009-05-18 ') | MonthName (2009-05-18) | MonthName (' 2009-13-18 ') | +-----------------------+-------------------------+-----------------------+-------------------------+ | May | May | NULL | NULL | +-----------------------+-------------------------+-----------------------+-------------------------+ 1 row in Set, 2 warnings (0.02 sec) */

I personally feel the same as month () ha. Just return the result is the name of the month, so of course it is in English.

The code is as follows	Copy Code
Select DayOfYear (20090518) as Day1,dayofyear (' 20090518 ') as Day2,dayofyear (' 2009-05-18 ') as Day3,dayofyear ( 20090518203000) as Day4,dayofyear (20091318) as Day5; /* +------+------+------+------+------+ | Day1 | Day2 | Day3 | Day4 | Day5 | +------+------+------+------+------+ | 138 | 138 | 138 | 138 | NULL | +------+------+------+------+------+ 1 row in Set, 1 Warning (0.02 sec) */

Well, DayOfMonth () and DayOfWeek () like this, no more examples. Well.

At the same time, as with MonthName (), there are also dayname () to obtain the name of the day (that is, days of the week) represented by that date.

The WEEK () function returns the week ordinal of a year for the specified date, and the Yeerweek () function returns which week of the year the specified date is:

The code is as follows

Copy Code

Select Week (20090301) as Week1,week (2009-03-01) as Week2,week (' 2009-03-01 ') as Week3,yearweek (20090518) as Week4, Yearweek (2009-05-18) as Week5,yearweek (' 2009-05-18 ') as week6; /* +-------+-------+-------+--------+-------+--------+ | Week1 | Week2 | Week3 | Week4 | Week5 | Week6 | +-------+-------+-------+--------+-------+--------+ | 9 | NULL | 9 | 200920 | NULL | 200920 | +-------+-------+-------+--------+-------+--------+ 1 row in Set, 2 warnings (0.02 sec) */

HOUR (), MINUTE (), and second () functions parse time values and return the parts of hours, minutes, and seconds, respectively:

The code is as follows	Copy Code
Select Hour (182300), second (123400), Minute (' 20:56 '); /* +--------------+----------------+-----------------+ | Hour (182300) | Second (123400) | Minute (' 20:56 ') | +--------------+----------------+-----------------+ | 18 | 0 | 56 | +--------------+----------------+-----------------+ 1 row in Set (0.00 sec) */

The values returned by week () and Yearweek () are usually between 0 and 53 (the number of weeks in a year is not clear), but

We can modify it to between 1 and 54, and we can also decide whether the one week starts from Sunday or Monday by selecting the second parameter of the function.

Time_to_sec ()--turn time into seconds, sec_to_time ()--convert seconds to an easy read time:

The code is as follows	Copy Code
Select Sec_to_time, Sec_to_time (3720), time_to_sec (' 24:01:10 '); /* +-----------------+-------------------+-------------------------+ | Sec_to_time (80) | Sec_to_time (3720) | Time_to_sec (' 24:01:10 ') | +-----------------+-------------------+-------------------------+ | 00:01:20 | 01:02:00 | 86470 | +-----------------+-------------------+-------------------------+ 1 row in Set (0.00 sec) */

Date addition and subtraction is what we often use, so what does MySQL give us the ready-made method? Let me tell you.
Date_add (), Date_sub ()--date addition minus:

The code is as follows

Copy Code

Select Date_add (' 2009-05-18 00:00:00 ', INTERVAL 6 MONTH); /* +--------------------------------------------------+ | Date_add (' 2009-05-18 00:00:00 ', INTERVAL 6 MONTH) | +--------------------------------------------------+ | 2009-11-18 00:00:00 | +--------------------------------------------------+ 1 row in Set (0.04 sec) */ Select Date_add (' 2009-05-18 00:00:00 ', Interval ' 03:45 ' Day_minute); /* +-----------------------------------------------------------------+ | Date_add (' 2009-05-18 00:00:00 ', Interval ' 03:45 ' Day_minute) | +-----------------------------------------------------------------+ | 2009-05-30 03:45:00 | +-----------------------------------------------------------------+ 1 row in Set (0.02 sec) */ Select Date_sub (' 2009-05-18 00:00:00 ', interval 6 hour); /* +-------------------------------------------------+ | Date_sub (' 2009-05-18 00:00:00 ', Interval 6 hour) | +-------------------------------------------------+ | 2009-05-17 18:00:00 | +-------------------------------------------------+ 1 row in Set (0.00 sec) */ Select Date_sub (' 2009-05-18 00:00:00 ', Interval ' 13-4 ' year_month); /* +-------------------------------------------------------------+ | Date_sub (' 2009-05-18 00:00:00 ', Interval ' 13-4 ' Year_month) | +-------------------------------------------------------------+ | 1996-01-18 00:00:00 | +-------------------------------------------------------------+ 1 row in Set (0.02 sec) */

The following query selects all records and its date_col value is within the last 30 days:

The code is as follows	Copy Code
Mysql> SELECT something from table WHERE To_days (now ())-To_days (Date_col) <= 30;

DayOfWeek (date)
Returns the week index of date (1= Sunday, 2 = Monday, ...). 7= Saturday). These index values correspond to ODBC standards.

The code is as follows	Copy Code
Mysql> Select DayOfWeek (' 1998-02-03 '); -> 3

Weekday (date)
Returns the week index of date (0= Monday, 1 = Tuesday, ...). 6= Sunday).

The code is as follows	Copy Code
Mysql> Select Weekday (' 1997-10-04 22:23:00 '); -> 5 Mysql> Select Weekday (' 1997-11-05 '); -> 2

DayOfMonth (date)
Returns the date in the month of date, in the range 1 through 31.

The code is as follows	Copy Code
Mysql> Select DayOfMonth (' 1998-02-03 '); -> 3

DayOfYear (date)
Returns the number of days in a year, in the range of 1 to 366.

The code is as follows	Copy Code
Mysql> Select DayOfYear (' 1998-02-03 '); -> 34

MONTH (date)
Returns the month of date, ranging from 1 to 12.

The code is as follows	Copy Code
Mysql> Select MONTH (' 1998-02-03 '); -> 2

Dayname (date)
Returns the name of the week of date.

The code is as follows	Copy Code
Mysql> Select Dayname ("1998-02-05"); -> ' Thursday '

MonthName (date)
Returns the month name of the date.

The code is as follows	Copy Code
Mysql> Select MonthName ("1998-02-05"); -> ' February '

Quarter (date)
Returns the quarter of date one year, ranging from 1 to 4.

The code is as follows	Copy Code
Mysql> Select quarter (' 98-04-01 '); -> 2

WEEK (date)
WEEK (Date,first)
For Sunday is the first day of the week, there is a single parameter that returns the week number of date, ranging from 0 to 52. 2 Parameter Form week () allows you to specify whether the week starts in Sunday or Monday. If the second argument is 0, the week begins in Sunday, if the second parameter is 1, starting from Monday.

The code is as follows	Copy Code
Mysql> Select WEEK (' 1998-02-20 '); -> 7 Mysql> Select WEEK (' 1998-02-20 ', 0); -> 7 Mysql> Select WEEK (' 1998-02-20 ', 1); -> 8

Year (date)
Returns the year of date, ranging from 1000 to 9999.

The code is as follows	Copy Code
Mysql> Select year (' 98-02-03 '); -> 1998

HOUR (Time)
Returns the hour of time, ranging from 0 to 23.

The code is as follows	Copy Code
Mysql> Select HOUR (' 10:05:03 '); -> 10

MINUTE (Time)
Returns the minutes of time, ranging from 0 to 59.

The code is as follows	Copy Code
Mysql> Select MINUTE (' 98-02-03 10:05:03 '); -> 5

SECOND (Time)
The number of seconds to return time, ranging from 0 to 59.

The code is as follows	Copy Code
Mysql> Select SECOND (' 10:05:03 '); -> 3

Period_add (P,n)
Increase n months to phase p (in format Yymm or yyyymm). Returns a value in YYYYMM format. Note that the phase parameter p is not a date value.

The code is as follows	Copy Code
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 a format yymm or yyyymm. Note that the time parameter P1 and P2 are not date values.

code is as follows	copy code
mysql> Select Period_diff (9802,199703); -> Date_add (Date,interval expr type) date_sub (date,interval expr type) Adddate (date,interval expr Type) subdate (date,interval expr type)