I. MySQL Gets the Current Date-Time Function

1.1 Get the current date + time

Function: now()

mysql> select now();
+---—+
| now() |
+---—+
| 2008-08-08 22:20:46 |
+---—+

In addition to the now() function to get the current date and time, MySQL has the following functions:

current_timestamp()
current_timestamp
localtime()
localtime
localtimestamp — (v4.0.6)
localtimestamp() — (v4.0.6)

mysql> select localtime();
+---—+
| localtime()         |
+---—+
| 2015-07-07 17:01:38 |
+---—+

mysql> select localtime;
+---—+
| localtime           |
+---—+
| 2015-07-07 17:01:41 |
+---—+
--------

These date-time functions are equivalent to now().

Given that the now() function is short and easy to remember, it is recommended to always use now() instead of the functions listed above.

1.2 Get the current date + time

Function: sysdate()

The sysdate() date-time function is similar to now(), except that the value is obtained at the beginning of execution and the value is dynamically obtained at the time of execution. Look at the following examples to see:

mysql> select now(), sleep(3), now();
+---—+-—-+---—+
| now() | sleep(3) | now() |
+---—+-—-+---—+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---—+-—-+---—+

mysql> select sysdate(), sleep(3), sysdate();
+---—+-—-+---—+
| sysdate() | sleep(3) | sysdate() |
+---—+-—-+---—+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---—+-—-+---—+
--------

As you can see, although the halfway sleep is 3 seconds, the time value of now() function is the same twice; the time value of sysdate() function is 3 seconds different twice. This is how sysdate() is described in MySQL Manual: Return the time at which the function executes.

sysdate() date-time function is seldom used in general.

1.3 Get the current date

Function: curdate()

mysql> select curdate();
+--+
| curdate() |
+--+
| 2008-08-08 |
+--+

The following two date functions are equivalent to curdate():

current_date()
current_date

1.4 Get the current time

Function: curtime()

mysql> select curtime();
+-—–+
| curtime() |
+-—–+
| 22:41:30 |
+-—–+

The following two time functions are equivalent to curtime():

current_time()
current_time

1.5 Get the current UTC date and time

Functions: utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()
+---—+--+--+---—+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---—+--+--+---—+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---—+--+--+---—+
--------

Because our country is located in the East Eighth Time Zone, the local time = UTC time + 8 hours. UTC time is very useful when the business involves many countries and regions.

2. MySQL Date and Time Extract (Selection) Function

2.1 Select each part of the date and time:

Date, time, year, quarter, month, day, hour, minute, second, microsecond

set @dt = '2008-09-10 07:15:30.123456′;
select date(@dt); — 2008-09-10
select time(@dt); — 07:15:30.123456
select year(@dt); — 2008
select quarter(@dt); — 3
select month(@dt); — 9
select week(@dt); — 36
select day(@dt); — 10
select hour(@dt); — 7
select minute(@dt); — 15
select second(@dt); — 30
select microsecond(@dt); — 123456
--------

2.2 MySQL Extract() function

Similar functions can be implemented above:

set @dt = '2008-09-10 07:15:30.123456′;

select extract(year from @dt); — 2008
select extract(quarter from @dt); — 3
select extract(month from @dt); — 9
select extract(week from @dt); — 36
select extract(day from @dt); — 10
select extract(hour from @dt); — 7
select extract(minute from @dt); — 15
select extract(second from @dt); — 30
select extract(microsecond from @dt); — 123456

select extract(year_month from @dt); — 200809
select extract(day_hour from @dt); — 1007
select extract(day_minute from @dt); — 100715
select extract(day_second from @dt); — 10071530
select extract(day_microsecond from @dt); — 10071530123456
select extract(hour_minute from @dt); — 715
select extract(hour_second from @dt); — 71530
select extract(hour_microsecond from @dt); — 71530123456
select extract(minute_second from @dt); — 1530
select extract(minute_microsecond from @dt); — 1530123456
select extract(second_microsecond from @dt); — 30123456
--------

MySQL Extract() function has no date(),time() function, other functions should be complete. It also has the function of selecting'day_microsecond'. Note that not only day and microsecond are selected here, but from the day part of the date to the microsecond part. Be tough enough!

The only downside of the MySQL Extract() function is that you need to hit the keyboard several times more.

2.3 MySQL dayof...

Functions: day of week (), day of month (), day of year ()

Return the date parameters in one week, one month, and one year, respectively.

set @dt = '2008-08-08′;

select dayofweek(@dt); — 6
select dayofmonth(@dt); — 8
select dayofyear(@dt); — 221

The date'2008-08-08'is the sixth day of the week (1 = Sunday, 2 = Monday,... 7 = Saturday; the eighth day of January; the twenty-first day of the year.
2.4 MySQL week...

Functions: week (), weekofyear (), day of week (), weekday (), yearweek ()

set @dt = '2008-08-08′;

select week(@dt); — 31
select week(@dt,3); — 32
select weekofyear(@dt); — 32
select dayofweek(@dt); — 6
select weekday(@dt); — 4
select yearweek(@dt); — 200831
--------

MySQL week() function, you can have two parameters, specific can see the manual. weekofyear() is the same as week(), which calculates the number of weeks a day is in a year. weekofyear(@dt) is equivalent to week(@dt,3).

The MySQL weekday() function, similar to dayofweek(), returns the location of "one day" in a week. The difference lies in the reference criteria:

• weekday: (0 = Monday, 1 = Tuesday, ..., 6 = Sunday)；
• dayofweek: (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

The MySQL year week () function returns the year(2008) + week location (31).

2.5 MySQL returns week and month names

Functions: dayname(), monthname()

set @dt = '2008-08-08′;

select dayname(@dt); — Friday
select monthname(@dt); — August

Think about how to return the Chinese name?

2.6 Return to the last day of the month

MySQL function: last_day()

select last_day('2008-02-01′); — 2008-02-29
select last_day('2008-08-08′); — 2008-08-31

The MySQL last_day() function is very useful. For example, I want to find out how many days are in the current month, so that I can calculate:

mysql> select now(), day(last_day(now())) as days;
+---—+-+
| now() | days |
+---—+-+
| 2008-08-09 11:45:45 | 31 |
+---—+-+

3. MySQL Date and Time Calculating Function

3.1 MySQL adds a time interval to the date

Function: date_add()

set @dt = now();

select date_add(@dt, interval 1 day); — add 1 day
select date_add(@dt, interval 1 hour); — add 1 hour
select date_add(@dt, interval 1 minute); — ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); — sub 1 day
--------

MySQL add date (), the addtime() function, can be replaced by date_add (). Here is an example of date_add() implementing addtime():

mysql> set @dt = '2008-08-09 12:12:33′;

mysql> select date_add(@dt, interval '01:15:30′ hour_second);
+--------+
| date_add(@dt, interval '01:15:30′ hour_second) |
+--------+
| 2008-08-09 13:28:03 |
+--------+

mysql> select date_add(@dt, interval '1 01:15:30′ day_second);
+---------+
| date_add(@dt, interval '1 01:15:30′ day_second) |
+---------+
| 2008-08-10 13:28:03 |
+---------+
--------

The date_add() function adds "1 hour, 15 minutes and 30 seconds" and "1 day, 1 hour, 15 minutes and 30 seconds" to @dt respectively. Recommendation: Always use date_add() date-time function instead of adddate(), addtime().

3.2 MySQL subtracts an interval from the date

Function: date_sub()

mysql> select date_sub('1998-01-01 00:00:00′, interval '1 1:1:1′ day_second);

+----------—-+
| date_sub('1998-01-01 00:00:00′, interval '1 1:1:1′ day_second) |
+----------—-+
| 1997-12-30 22:58:59 |
+----------—-+

MySQL date_sub() date-time function and date_add() are used in the same way, no more details. In addition, there are two functions in MySQL, sub date (), subtime (), which are suggested to be replaced by date_sub().

3.3 MySQL Alternative Date

Functions: period_add(P,N), period_diff(P1,P2)

The function parameter "P" is in the form of "YYYYMM" or "YYMM", and the second parameter "N" means adding or subtracting N month s.

MySQL period_add(P,N): Date plus/minus N months.
 

mysql> select period_add(200808,2), period_add(20080808,-2)
+---—-+-----+
| period_add(200808,2) | period_add(20080808,-2) |
+---—-+-----+
| 200810 | 20080806 |
+---—-+-----+

MySQL period_diff(P1,P2): date P1-P2，Return N Months.
mysql> select period_diff(200808, 200801);
+----—–+
| period_diff(200808, 200801) |
+----—–+
| 7 |
+----—–+

In MySQL, these two date functions are rarely used.

3.4 MySQL Date and Time Subtraction

Functions: datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2): Subtraction of two dates date1 – date2，Returns the number of days.

select datediff('2008-08-08′, '2008-08-01′); — 7

select datediff('2008-08-01′, '2008-08-08′); — -7



MySQL timediff(time1,time2): Subtraction of two dates time1 – time2，Return time Difference.

select timediff('2008-08-08 08:08:08′, '2008-08-08 00:00:00′); — 08:08:08

select timediff('08:08:08′, '00:00:00′); — 08:08:08

Note: The two parameter types of the timediff(time1,time2) function must be the same.

4. MySQL Date Conversion Function and Time Conversion Function

4.1 MySQL (time, seconds) conversion

Functions: time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05′); — 3605

select sec_to_time(3605); — '01:00:05′

4.2 MySQL (Date, Date) Conversion

Function: to_days(date), from_days(days)

select to_days('0000-00-00′); — 0

select to_days('2008-08-08′); — 733627



select from_days(0); — '0000-00-00′

select from_days(733627); — '2008-08-08′

4.3 MySQL Str to Date (string to date)

Function: str_to_date(str, format)

select str_to_date('08/09/2008′, '%m/%d/%Y'); — 2008-08-09

select str_to_date('08/09/08′ , '%m/%d/%y'); — 2008-08-09

select str_to_date('08.09.2008′, '%m.%d.%Y'); — 2008-08-09

select str_to_date('08:09:30′, '%h:%i:%s'); — 08:09:30

select str_to_date('08.09.2008 08:09:30′, '%m.%d.%Y %h:%i:%s'); — 2008-08-09 08:09:30

As you can see, the str_to_date(str,format) conversion function can convert some chaotic strings into date format. In addition, it can also be converted to time. "Format" can be found in the MySQL manual.

4.4 MySQL Date/Time to Str (date/time converted to string)

Functions: date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00′, '%W %M %Y');
+--------+
| date_format('2008-08-08 22:23:00′, '%W %M %Y') |
+--------+
| Friday August 2008 |
+--------+

mysql> select date_format('2008-08-08 22:23:01′, '%Y%m%d%H%i%s');
+--------—-+
| date_format('2008-08-08 22:23:01′, '%Y%m%d%H%i%s') |
+--------—-+
| 20080808222301 |
+--------—-+

mysql> select time_format('22:23:01′, '%H.%i.%s');
+-------+
| time_format('22:23:01′, '%H.%i.%s') |
+-------+
| 22.23.01 |
+-------+

MySQL date/time conversion function: date_format(date,format), time_format(time,format) can convert a date/time into a variety of string formats. It is a reversal of the str_to_date(str,format) function.

4.5 MySQL Gets National Area Time Format

Function: get_format()

MySQL get_format() grammar:

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

All examples of MySQL get_format() usage:

select get_format(date,'usa') ; — '%m.%d.%Y'
select get_format(date,'jis') ; — '%Y-%m-%d'
select get_format(date,'iso') ; — '%Y-%m-%d'
select get_format(date,'eur') ; — '%d.%m.%Y'
select get_format(date,'internal') ; — '%Y%m%d'
select get_format(datetime,'usa') ; — '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis') ; — '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso') ; — '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur') ; — '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ; — '%Y%m%d%H%i%s'
select get_format(time,'usa') ; — '%h:%i:%s %p'
select get_format(time,'jis') ; — '%H:%i:%s'
select get_format(time,'iso') ; — '%H:%i:%s'
select get_format(time,'eur') ; — '%H.%i.%s'
select get_format(time,'internal') ; — '%H%i%s'

MySQL get_format() function has less chance to be used in practice.

4.6 MySQL patchwork date and time

Functions: makdedate (year, day of year), maketime (hour, minute, second)

select makedate(2001,31); — '2001-01-31′
select makedate(2001,32); — '2001-02-01′
select maketime(12,15,30); — '12:15:30′

MySQL Timestamp function

5.1 MySQL gets the current timestamp

Function: current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();
+---—+---—+
| current_timestamp | current_timestamp() |
+---—+---—+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---—+---—+

5.2 MySQL (Unix timestamp, date) conversion function:

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

The following is an example:

select unix_timestamp(); — 1218290027
select unix_timestamp('2008-08-08′); — 1218124800
select unix_timestamp('2008-08-08 12:30:00′); — 1218169800

select from_unixtime(1218290027); — '2008-08-09 21:53:47′
select from_unixtime(1218124800); — '2008-08-08 00:00:00′
select from_unixtime(1218169800); — '2008-08-08 12:30:00′
select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); — '2008 8th August 12:30:00 2008′

5.3 MySQL timestamp conversion, addition and subtraction functions:

timestamp(date) — date to timestamp
timestamp(dt,time) — dt + time
timestampadd(unit,interval,datetime_expr) –
timestampdiff(unit,datetime_expr1,datetime_expr2) –

Example section:

select timestamp('2008-08-08′); — 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00′, '01:01:01′); — 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00′, '10 01:01:01′); — 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00′); — 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00′, interval 1 day); — 2008-08-09 08:00:00

The MySQL timestampadd() function is similar to date_add().

select timestampdiff(year,'2002-05-01′,'2001-01-01′); — -1
select timestampdiff(day ,'2002-05-01′,'2001-01-01′); — -485
select timestampdiff(hour,'2008-08-08 12:00:00′,'2008-08-08 00:00:00′); — -12
select datediff('2008-08-08 12:00:00′, '2008-08-01 00:00:00′); — 7

The MySQL timestampdiff() function is much more powerful than datediff(), which only counts the days between two date s.

MySQL Time Zone Conversion Function

convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00′, '+08:00′, '+00:00′); — 2008-08-08 04:00:00

Time zone conversion can also be achieved by date_add, date_sub, timestampadd.

select date_add('2008-08-08 12:00:00′, interval -8 hour); — 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00′, interval 8 hour); — 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00′); — 2008-08-08 04:00:00

select timediff('23:40:00′, ' 18:30:00′); — Two-time subtraction
SELECT substring( timediff('23:40:00′, ' 18:30:00′),1,5) —-"05: 10"Subtract return hours: minutes
select datediff('2008-08-08′, '2008-08-01′); — 7      —–Subtraction of two dates
select TO_DAYS('2008-09-08′)-TO_DAYS('2008-08-08′)     —–Subtraction of two dates
SELECT substring( '2009-06-17 10:00:00′,   1,   10   )   —-from datetime Extract "date"

The time stamp is the number of seconds from January 1, 1970 to the target time.
Two datetime intervals can be computed