Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to obtain the current date and date format, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Get system date: NOW ()

Format date: DATE_FORMAT (date, format)

Note: date: time field

Format: date format

Returns the system date and outputs 2009-12-25 14:38:59

Select now ()

Output 09-12-25

Select date_format (now (),'% ymuri% mmury% d')

Format the date value according to the format string:

% S,% s two-digit seconds (000001,..., 59)

% I,% I score in two-digit form (005001,..., 59)

% H double-digit hours, 24 hours (000001,., 23)

% h double-digit hours, 12 hours (01Jing 02,., 12)

% k hours in digital form, 24 hours (0Jol 1,..., 23)

L hours in digital form, 12 hours (1, 2,., 12)

% T 24-hour time form (hh:mm:ss)

% r time form of 12 hours (hh:mm:ss AM or hh:mm:ss PM)

% p AM or PM

% W name of every day of the week (Sunday, Monday,..., Saturday)

% an abbreviation of the name of every day of the week (Sun, Mon,..., Sat)

% d double digits indicate the number of days in the month (0001, 31)

The numeric form of% e represents the number of days in the month (1, 2,. 31)

The% D suffix denotes the number of days in the month (1st, 2nd, 3Rd, etc.)

% w represents the number of days of the week in numeric form (0 = Sunday, 1=Monday,..., 6=Saturday)

% j represents the number of days of the year in three digits (00002,., 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)

Month represented by% c number (1, 2,., 12)

Year represented by% Y four digits

Year represented by% y double digits

% Direct value'%'

Curdate ()

MySQL gets the current date-time function

1.1 get the current date + time (date + time) function: now ()

Mysql > select now ()

+-+

| | now () |

+-+

| | 2008-08-08 22:20:46 |

+-+

In addition to the now () function that gets the current date and time, there are the following functions in MySQL:

Current_timestamp ()

, current_timestamp

, localtime ()

, localtime

, localtimestamp-(v4.0.6)

, localtimestamp ()-- (v4.0.6)

These date-time functions are all equivalent to now (). Since the now () function is short and easy to remember, it is recommended that you always use now () instead of the functions listed above.

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

The sysdate () date-time function is similar to now (), except that now () gets the value at the beginning of execution, and sysdate () gets the value dynamically when the function is executed. Take a look at the following example:

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 | |

+-+

You can see that although the sleep is 3 seconds midway, the time value of the now () function is the same twice; the time value obtained by the sysdate () function differs by 3 seconds. This is how sysdate () is described in MySQL Manual: Return the time at which the functionexecutes.

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

two。 Get current date (date) function: curdate ()

Mysql > select curdate ()

+-+

| | curdate () |

+-+

| | 2008-08-08 | |

+-+

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

Current_date ()

, current_date

3. Get current time (time) function: curtime ()

Mysql > select curtime ()

+-+

| | curtime () |

+-+

| | 22:41:30 |

+-+

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

Current_time ()

, current_time

4. 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 eight time zone, so the local time = UTC time + 8 hours. UTC time is very useful when business involves multiple countries and regions.

Second, MySQL date and time Extract (select) function.

1. Select each part of the date time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2008-09-100 07 purl 1530.123456'

Select date (@ dt);-- 2008-09-10

Select time (@ dt);-- 07Viru 1530.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 achieve similar functions as above:

Set @ dt = '2008-09-100 07 purl 1530.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)-- 123456select 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);

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

Except for the functions of date () and time (), the MySQLExtract () function should have all other functions. It also has the function of selecting 'day_microsecond'' and so on. Note that not just day and microsecond are selected here, but all the way from the day section of the date to the microsecond section. How tough it is!

The only downside of the MySQL Extract () function is that you need to type the keyboard a few more times.

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

Returns the position of the date parameter in a week, a month, or a year, respectively.

Set @ dt = '2008-08-08'

Select dayofweek (@ dt);-- 6

Select dayofmonth (@ dt);-- 8

Select dayofyear (@ dt);--

The date '2008-08-08' is the sixth day of the week (1 = Sunday, 2 = Monday, …, 7 = Saturday); the eighth day of January; and the 221st day of the year.

4. MySQL week... Functions: week (), weekofyear (), dayofweek (), 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

The MySQL week () function, which can have two parameters, can be found in the manual. Weekofyear (), like week (), calculates the week in which "some day" is located. Weekofyear (@ dt) is equivalent to week (@ dt,3).

The MySQLweekday () function, like dayofweek (), returns the position of "someday" in the week. The difference lies in the reference standard, weekday: (0 = Monday, 1 = Tuesday, … , 6 = Sunday); dayofweek: (1 = Sunday, 2 = Monday, … , 7 = Saturday)

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

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

Set @ dt = '2008-08-08'

Select dayname (@ dt);-- Friday

Select monthname (@ dt);-- August

Think about it, how to return the Chinese name?

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

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 know how many days there are in the current month, which can be calculated like this:

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

+-+ +

| | now () | days | |

+-+ +

| | 2008-08-09 11:45:45 | 31 | |

+-+-+ III. MySQL date and time calculation function

1. MySQL adds a time interval to 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

The MySQL adddate (), addtime () function can be replaced by date_add (). Here is an example of how date_add () implements the addtime () function:

Mysql > set @ dt = '2008-08-09 12purl 1233'

Mysql >

Mysql > select date_add (@ dt, interval '01VO1VO1VERV 30' hour_second)

+-- +

| | date_add (@ dt, interval'01, interval'01, 15 hour_second) | |

+-- +

| | 2008-08-09 13:28:03 |

+-+ mysql > select date_add (@ dt, interval'01 dt 15 day_second)

+-- +

| | date_add (@ dt, interval'1 01v 1515 day_second) |

+-- +

| | 2008-08-10 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-time function instead of adddate (), addtime ().

2. MySQL subtracts a time interval from the date: date_sub ()

Mysql > select date_sub ('1998-01-01 00 day_second, interval' 1 1)

+-- +

| | date_sub ('1998-01-01 00 day_second, interval' 1) |

+-- +

| | 1997-12-30 22:58:59 |

+-- +

The MySQL date_sub () date-time function is the same as date_add (), so I won't repeat it. In addition, there are two functions in MySQL, subdate () and subtime (), which are recommended to be replaced by date_sub ().

3. MySQL alternative date function: period_add (PMagne N), period_diff (P1MaginP2)

The function parameter "P" is formatted as "YYYYMM" or "YYMM", and the second parameter "N" adds or subtracts N month (month).

MySQL period_add (N): date plus / minus N month.

Mysql > select period_add (200808jue 2), period_add (20080808mai Murray 2)

+-+ +

| | period_add (200808jue 2) | period_add (20080808mai2) | |

+-+ +

| | 200810 | 20080806 | |

+-+ +

MySQL period_diff (P1Magol 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.

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

MySQL datediff (date1,date2): subtracts date1-date2 from two dates to return 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): subtracts time1-time2 from two dates and returns the time difference.

Select timediff ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08) / (/ select timediff /-- 08:08:08

Select timediff ('08rig 08 08mm,' 001R 00R 00');-- 08:08:08

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

4. 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 ('01VO 05');-- 3605

Select sec_to_time (3605);-- '01VRO 05'

2. MySQL (date, days) conversion function: to_days (date), from_days (days)

Select to_days ('0000-00');-- 0

Select to_days ('2008-08-08');-- 733627select from_days (0);-- '0000-00-00'

Select from_days (733627);-'2008-08-08'

3. MySQL Str to Date (string conversion to date) function: str_to_date (str, format)

Select str_to_date ('08Compact 09Accord 2008,'% m _ swap% dmax% Y');-- 2008-08-09

Select str_to_date ('08Compact 09Compact 08','% mAccord% dash% y');-- 2008-08-09

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

Select str_to_date ('08 09 30 minutes,'% HVA% iRO% s');-- 08:09:30

Select str_to_date ('08.09.2008 08 select str_to_date 09,'% m.%d.%Y% HV% iRO% s');-- 2008-08-09 08:09:30

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

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

Mysql > select date_format ('2008-08-08 22 2300mm,'% W% M% Y')

+-- +

| | date_format ('2008-08-08 22 2300,'% W% M% Y') |

+-- +

| | Friday August 2008 |

+-+ mysql > select date_format ('2008-08-08 22 purl 23 purl 01mm,'% Y% m% d% H% I% s')

+-- +

| | date_format ('2008-08-08 22 23lane 01mm,'% Y% m% d% H% I% s') |

+-- +

| | 20080808222301 | |

+-+ mysql > select time_format ('22 _ () _ 23 _

+-+

| | time_format ('22 _ 22 _

+-+

| | 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 an inverse transformation of the str_to_date (str,format) function.

5. MySQL obtains the time format function of country and region: get_format ()

MySQL get_format () syntax:

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

All examples of the use of MySQL get_format ():

Select get_format (date,'usa');--'% m.d.% Y'

Select get_format (date,'jis');--'% Ymuri% mmury% d'

Select get_format (date,'iso');--'% Ymuri% mmury% 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% HRV% iRU% s'

Select get_format (datetime,'iso');--'% Y-%m-%d% HRV% iRU% 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');--'% HRV% iRO% s'

Select get_format (time,'iso');--'% HRV% iRO% s'

Select get_format (time,'eur');--'% H.I.% s'

Select get_format (time,'internal');--'% H% I% s'

The MySQL get_format () function uses fewer opportunities in practice.

6. MySQL piecing together date and time functions: makdedate (year,dayofyear), maketime (hour,minute,second)

Select makedate (2001-31);-- '2001-01-31'

Select makedate (2001jue 32);-- '2001-02-01'select maketime (12pas 15-30);--' 12 MySQL 1515-30'V. MySQL timestamp (Timestamp) function

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 | |

+-+ +

2. MySQL (Unix timestamp, date) conversion function:

Unix_timestamp ()

Unix_timestamp (date)

From_unixtime (unix_timestamp)

From_unixtime (unix_timestamp,format)

Here is an example:

Select unix_timestamp ();-- 1218290027

Select unix_timestamp ('2008-08-08');-1218124800

Select unix_timestamp ('2008-08-08 12 30 00');-- 1218169800select from_unixtime (1218290027);--' 2008-08-09 21 V 53 47'

Select from_unixtime (1218124800);-- '2008-08-08 00001

Selectfrom_unixtime (1218169800);-- '2008-08-08 12:30:00'selectfrom_unixtime (1218169800,'% Y% D% M% h:%i:%s% x');-- '2008 8th August12:30:00 2008'

3. MySQL timestamp (timestamp) conversion, increment and subtraction of functions:

Timestamp (date)-date to timestamp

Timestamp (dt,time)-- dt + time

Timestampadd (unit,interval,datetime_expr)-

Timestampdiff (unit,datetime_expr1,datetime_expr2)-

Take a look at the example section:

Select timestamp ('2008-08-08');-- 2008-08-08 00:00:00

Select timestamp ('2008-08-08 / 08 / 08 / 08 / 08 / 02 / 02 / 01 / 08 / 08 / 08 / 02 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01);-- 2008-08-08 09:01:01

Selecttimestamp ('2008-08-08-08 day,' 100-01-08);-- 2008-08-00 (day, 1, '2008-08-08-08-08);-- 2008-08-09 08:00:00

Select date_add ('2008-08-08-08 select date_add, interval 1 day);-- 2008-08-09 08:00:00

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

Select timestampdiff (year,'2002-05-01);-1

Select timestampdiff (day, 2002-05-01)

Select timestampdiff (hour,'2008-08-08 12);-12

Select datediff ('2008-08-08 12-0-00,' 2008-08-01-00);-- 7

The MySQL timestampdiff () function is much more powerful than datediff (), which can only calculate the number of days between two dates (date).

6. The MySQL time zone (timezone) conversion function convert_tz (dt,from_tz,to_tz) selectconvert_tz ('2008-08-08 12, 00-00,' + 08-08, + 00-00);-- 2008-08-0804

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

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

Select date_sub ('2008-08-08 1200 hour, interval 8);-- 2008-08-08 04:00:00

Select timestampadd (hour,-8, '2008-08-08 12 0000');-- 2008-08-08 04:00:00

Thank you for reading this article carefully. I hope the article "how to get the current date and date format by MySQL" shared by the editor will be helpful to you. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.