[Reprinted] mysql obtains the current date and formats it.

Blog. csdn. netkangbrotherarticledetails7030304MYSQL obtains the current date and date format. system date: NOW () formatted date: DATE_FORMAT (date, format) Note: date: Time Field format: returns the system date, output 2009-12-2514: 38: 59 selectnow (); Output 09-12

Http://blog.csdn.net/kangbrother/article/details/7030304 MYSQL get current date and date format get system date: NOW () type date: DATE_FORMAT (date, format) Note: date: Time Field format: date type return system date, output select 12-25 14:38:59 select now (); Output 09-12

Http://blog.csdn.net/kangbrother/article/details/7030304

MYSQL obtains the current date and Date Format
Get system date: NOW ()
Format date: DATE_FORMAT (date, format)
Note: date: Time Field
Format: Date format

Returns the system date, which is 14:38:59.
Select now ();
Output 09-12-25
Select date_format (now (), '% y-% m-% D ');

Format the date value based on the format string:

% S, % s two-digit second (,..., 59)
% I, % I two-digit score (,..., 59)
% H two-digit hour, 24 hours (,..., 23)
% H two-digit hour, 12 hours (,..., 12)
% K, 24 hours (0, 1,..., 23)
% L hour in numeric format (1, 2,..., 12)
% T 24-hour time format (hh: mm: ss)
% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)
% P AM or PM
% W name of each day in a week (Sunday, Monday,..., Saturday)
% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)
% D indicates the number of days in a month (00, 01,..., 31)
% E indicates the number of days in a month (1, 2,..., 31)
% D indicates the number of days in a month (1st, 2nd, 3rd ,...)
% W represents the number of days in a week (0 = Sunday, 1 = Monday,..., 6 = Saturday)
% J represents the number of days in a year in three digits (001,002,..., 366)
% U Week (0, 1, 52), where Sunday is the first day of the week
% U Week (0, 1, 52), where Monday is the first day of the week
% M month name (January, February,..., December)
% B abbreviated month name (January, February,..., December)
% M double-digit month (01, 02,..., 12)
% C indicates the month (1, 2,..., 12)
The year in the format of % Y.
The year in double-digit % y format.
% Direct value "%"

Curdate ()

MySQL obtains the current date and time function
1.1 obtain the current date + time (date + time) function: now ()

Mysql> select now ();

+ --------------------- +
| Now () |
+ --------------------- +
| 22:20:46 |
+ --------------------- +
In addition to the now () function, MySQL also has the following functions:

Current_timestamp ()
, Current_timestamp
, Localtime ()
, Localtime
, Localtimestamp -- (v4.0.6)
, Localtimestamp () -- (v4.0.6)
These datetime functions are equivalent to now (). Considering that the now () function is short and easy to remember, we recommend that you always use now () to replace the functions listed above.

1.2 obtain the current date + time (date + time) function: sysdate ()

The sysdate () Date and time function is similar to now (). The difference is that now () is obtained at the execution start time, And sysdate () dynamically obtains the value during function execution. You can see the following example:

Mysql> select now (), sleep (3), now ();

+ --------------------- + ---------- + --------------------- +
| Now () | sleep (3) | now () |
+ --------------------- + ---------- + --------------------- +
| 22:28:21 | 0 | 22:28:21 |
+ --------------------- + ---------- + ------------------- + Mysql> select sysdate (), sleep (3), sysdate ();

+ --------------------- + ---------- + --------------------- +
| Sysdate () | sleep (3) | sysdate () |
+ --------------------- + ---------- + --------------------- +
| 22:28:41 | 0 | 22:28:44 |
+ --------------------- + ---------- + --------------------- +
We can see that although sleep lasts for 3 seconds, the time values of the now () function are the same. The time values obtained by the sysdate () function differ by 3 seconds. In MySQL Manual, sysdate () is described as follows: Return the time at which the functionexecutes.

Sysdate () Date and time functions are rarely used.

2. Obtain 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
3. Obtain 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
4. Obtain 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 () |
+ --------------------- + ------------ + --------------------- +
| 14:47:11 | 14:47:11 | 22:47:11 |
+ --------------------- + ------------ + --------------------- +
Because our country is located in the east eight time zone, the local time = UTC time + 8 hours. UTC time is very useful when your business involves multiple countries and regions.

Ii. MySQL Date and Time Extract (select) function.
1. Select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2017-09-10 07:15:30. 123 ';

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. MySQL Extract () function, which can implement similar functions:

Set @ dt = '2017-09-10 07:15:30. 123 ';

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
In addition to date () and time () functions, the MySQLExtract () function must be fully functional. You can also select 'day _ microsecond. Note that we will not only select day and microsecond, but choose from day of the date to microsecond. Powerful enough!

The only bad thing about the MySQL Extract () function is that you need to press the keyboard several more times.

3. MySQL dayof... Function: dayofweek (), dayofmonth (), dayofyear ()

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

Set @ dt = '2017-08-08 ';

Select dayofweek (@ dt); -- 6
Select dayofmonth (@ dt); -- 8
Select dayofyear (@ dt); -- 221
The date '2017-08-08 'is the first day of the week (1 = Sunday, 2 = Monday ,..., 7 = Saturday); January days in 8th; 221st days in a year.

4. MySQL week... Function: week (), weekofyear (), dayofweek (), weekday (), yearweek ()

Set @ dt = '2017-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
The MySQL week () function has two parameters. For details, refer to the manual. Similar to weekofyear () and week (), weekofyear is the day of the year in which the calculation "one day" is located. Weekofyear (@ dt) is equivalent to week (@ dt, 3 ).

The MySQLweekday () function is similar to dayofweek () and returns the position of "one day" in a week. The difference lies in the reference standard, weekday :( 0 = Monday, 1 = Tuesday ,..., 6 = Sunday); dayofweek :( 1 = Sunday, 2 = Monday ,..., 7 = Saturday)

MySQL yearweek () function, returns year (2008) + week position (31 ).

5. MySQL returns the week and month name functions: dayname (), monthname ()

Set @ dt = '2017-08-08 ';

Select dayname (@ dt); -- Friday
Select monthname (@ dt); -- August
Think about how to return the Chinese name?

6. MySQL last_day () function: returns the last day of the month.

Select last_day ('2017-02-01 '); -- 2008-02-29
Select last_day ('2017-08-08 '); -- 2008
The MySQL last_day () function is very useful. For example, you can calculate the number of days in the current month as follows:

Mysql> select now (), day (last_day (now () as days;

+ --------------------- + ------ +
| Now () | days |
+ --------------------- + ------ +
| 11:45:45 | 31 |
+ --------------------- + ------ + 3. MySQL Date and Time Calculation Function
1. MySQL adds a time interval for the date: 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 adddate () and addtime () functions can be replaced by date_add. The following is an example of addtime () implemented by date_add:

Mysql> set @ dt = '2017-08-09 12:12:33 ';

Mysql>
Mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second );

+ ------------------------------------------------ +
| Date_add (@ dt, interval '01: 15: 30 'hour_second) |
+ ------------------------------------------------ +
| 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) |
+ ------------------------------------------------- +
| 13:28:03 |
+ ------------------------------------------------- +
The date_add () function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @ dt respectively ". Suggestion: always use the date_add () Date and Time Function to replace adddate () and addtime ().

2. MySQL is a date minus a time interval: date_sub ()

Mysql> select date_sub ('2017-01-01 00:00:00 ', interval '1' day_second );

+ ---------------------------------------------------------------- +
| Date_sub ('2017-01-01 00:00:00 ', interval '1' day_second) |
+ ---------------------------------------------------------------- +
| 22:58:59 |
+ ---------------------------------------------------------------- +
The usage of MySQL date_sub () datetime function is the same as that of date_add. In addition, there are two subdate () and subtime () functions in MySQL. We recommend that you use date_sub () instead.

3. MySQL alternative date functions: period_add (P, N), period_diff (P1, P2)

The format of the function parameter "P" is "YYYYMM" or "YYMM". The second parameter "N" indicates adding or subtracting N month ).

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

Mysql> select period_add (20080808), period_add (,-2)

+ ---------------------- + ------------------------- +
| Period_add (20080808) | period_add (,-2) |
+ ---------------------- + ------------------------- +
| 200810/20080806 |
+ ---------------------- + ------------------------- +
MySQL period_diff (P1, P2): Date P1-P2, returns N months.

Mysql> select period_diff (200808,200 );

+ ----------------------------- +
| Period_diff (200808,200) |
+ ----------------------------- +
| 7 |
+ ----------------------------- +
In MySQL, these two date functions are rarely used.

4. MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2)

MySQL datediff (date1, date2): two dates subtract date1-date2 and return the number of days.

Select datediff ('2017-08-08 ', '2017-08-01'); -- 7
Select datediff ('2017-08-01 ', '2017-08-08'); ---7
MySQL timediff (time1, time2): two dates minus time1-time2, return the time difference.

Select timediff ('2017-08-08 08:08:08 ', '2017-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.

Iv. MySQL date conversion function and time Conversion Function
1. MySQL (time, second) Conversion Function: time_to_sec (time), sec_to_time (seconds)

Select time_to_sec ('01: 00: 05 '); -- 3605
Select sec_to_time (3605); -- '01: 00: 05'
2. MySQL (date, number of days) conversion functions: to_days (date), from_days (days)

Select to_days ('2014-00-00 '); -- 0
Select to_days ('2014-08-08 '); -- 2008 select from_days (0); -- '2014-00-00'
Select from_days (733627); -- '2017-08-08'
3. MySQL Str to Date (string converted to Date) function: str_to_date (str, format)

Select str_to_date ('2014/1/123', '% m/% d/% y'); --
Select str_to_date ('2014/1/08', '% m/% d/% y'); -- 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'); -- 08:09:30
We can see that the str_to_date (str, format) Conversion Function can convert disordered strings into date formats. In addition, it can be converted to time. For "format", see the MySQL manual.

4. MySQL Date/Time to Str (convert date/time to string) functions: date_format (Date, format), time_format (Time, format)

Mysql> select date_format ('2017-08-08 22:23:00 ',' % W % M % y ');

+ ------------------------------------------------ +
| Date_format ('2017-08-08 22:23:00 ',' % W % M % y') |
+ ------------------------------------------------ +
| Friday August 2008 |
+ -------------------------------------------- + Mysql> select date_format ('2017-08-08 22:23:01 ',' % Y % m % d % H % I % s ');

+ ---------------------------------------------------- +
| Date_format ('2017-08-08 22:23:01 ',' % Y % m % d % H % I % s') |
+ ---------------------------------------------------- +
| 1, 20080808222301 |
+ ------------------------------------------------ + Mysql> select time_format ('22: 23: 01', '% H. % I. % s ');

+ ------------------------------------- +
| Time_format ('22: 23: 01', '% H. % I. % s') |
+ ------------------------------------- +
| 22.23.01 |
+ ------------------------------------- +
MySQL date and time conversion functions: date_format (date, format), time_format (time, format) can convert a date/time to a variety of string formats. It is an inverse conversion of the str_to_date (str, format) function.

5. MySQL returns the country/region time format function: get_format ()

MySQL get_format () Syntax:

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'
The MySQL get_format () function has fewer opportunities in practice.

6. MySQL patchwork Date and Time Functions: makdedate (year, dayofyear), maketime (hour, minute, second)

Select makedate (2001); -- '2017-01-31'
Select makedate (2001); -- '2014-02-01 'select maketime (, 30); -- '12: 15: 30' v. MySQL Timestamp Function
1. MySQL returns the current timestamp function: current_timestamp, current_timestamp ()

Mysql> select current_timestamp, current_timestamp ();

+ --------------------- +
| Current_timestamp () |
+ --------------------- +
| 23:22:24 | 23:22:24 |
+ --------------------- +
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 ('2017-08-08 '); -- 2008
Select unix_timestamp ('2017-08-08 12:30:00 '); -- 2008 select from_unixtime (1218169800); -- '2017-08-09 21:53:47'
Select from_unixtime (1218124800); -- '2017-08-08 00:00:00'
Selectfrom_unixtime (1218169800); -- '2017-08-08 12:30:00 'selectfrom _ unixtime (2008,' % Y % D % M % h: % I: % s % x '); -- '1970 8th August12: 30: 00 100'
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 )--
See the example section:

Select timestamp ('2017-08-08 '); -- 2008 00:00:00
Select timestamp ('2017-08-08 08:00:00 ', '01: 01: 01'); -- 2008 09:01:01
Selecttimestamp ('2017-08-08 08:00:00 ', '10 01:01:01'); -- 2008-08-2008: 01: 01 select timestampadd (day, 1, '2017-08-08 08:00:00 '); -- 2008 08:00:00
Select date_add ('2017-08-08 08:00:00 ', interval 1 day); -- 2008 08:00:00
The MySQL timestampadd () function is similar to date_add ().

Select timestampdiff (year, '2017-05-01 ', '2017-01-01'); ---1
Select timestampdiff (day, '2017-05-01 ', '2017-01-01'); ---2002
Select timestampdiff (hour, '2017-08-08 12:00:00 ', '2017-08-08 00:00:00'); ---12

Select datediff ('2017-08-08 12:00:00 ', '2017-08-01 00:00:00'); -- 7
MySQL timestampdiff () functions are much more powerful than datediff () functions. datediff () can only calculate the number of days for the difference between two dates.

Vi. MySQL timezone Conversion Function convert_tz (dt, from_tz, to_tz) selectconvert_tz ('2017-08-08 12:00:00 ',' + ',' + '); -- 2008-08-0804: 00: 00
You can also use date_add, date_sub, and timestampadd to convert time zones.

Select date_add ('2017-08-08 12:00:00 ', interval-8 hour); -- 2008 04:00:00
Select date_sub ('2017-08-08 12:00:00 ', interval 8 hour); -- 2008 04:00:00
Select timestampadd (hour,-8, '2017-08-08 12:00:00 '); -- 2008 04:00:00