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

This article introduces the knowledge of "how to get the current time and timestamp by MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Get the current date + time (date + time) function: now ()

MySQL > select now (); +-+ | now () | +-+ | 2013-04-08 20:56:19 | +-+

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.

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 () | +-04-08 20:57:46 | 0 | 2013-04-08 20:57:46 | +-+-+ mysql > select sysdate (), sleep (3), sysdate () +-+ | sysdate () | sleep (3) | sysdate () | +-04-08 20:58:47 | 0 | 2013-04-08 20:58:50 | +-- +-+

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 function executes.

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

Get current date (date) function: curdate ()

Mysql > select curdate (); +-+ | curdate () | +-+ | 2013-04-08 | +-+

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

Current_date (), current_date

Get current time (time) function: curtime ()

Mysql > select curtime (); +-+ | curtime () | +-+ | 21:00:11 | +-+

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

Current_time (), current_time

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 () | 2013-04-08 13:01:32 | 2013-04-08 | 13:01:32 | 2013-04-08 21:01:32 | + +-+ +

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.

MySQL date 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-1007 date 1530.123456 selection date (@ dt);-2008-09-10select time (@ dt);-07:15:30.123456select year (@ dt);-2008select quarter (@ dt);-3select month (@ dt);-9select week (@ dt);-36select day (@ dt);-10select hour (@ dt);-7select minute (@ dt);-15select second (@ dt);-30select microsecond (@ dt);-123456

2. MySQL Extract () function, which can achieve similar functions as above:

Et @ dt = '2008-09-1007 extract 1530.123456);-2008select extract (quarter from @ dt);-3select extract (month from @ dt);-9select extract (week from @ dt);-36select extract (day from @ dt);-10select extract (hour from @ dt);-7select extract (minute from @ dt);-15select extract (second from @ dt);-30select extract (microsecond from @ dt);-123456select extract (year_month from @ dt) -200809select extract (day_hour from @ dt);-1007select extract (day_minute from @ dt);-100715select extract (day_second from @ dt);-10071530select extract (day_microsecond from @ dt);-10071530123456select extract (hour_minute from @ dt);-715select extract (hour_second from @ dt);-71530select extract (hour_microsecond from @ dt);-71530123456select extract (minute_second from @ dt);-1530select extract (minute_microsecond from @ dt);-1530123456select extract (second_microsecond from @ dt) -30123456

Except for the functions of date () and time (), the MySQL Extract () 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);-6select dayofmonth (@ dt);-8select 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; and the 221st day of the year.

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

Set @ dt = '2008-08-08) select week (@ dt);-31select week (@ dt,3);-32select weekofyear (@ dt);-32select dayofweek (@ dt);-6select weekday (@ dt);-4select 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 MySQL weekday () 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);-Fridayselect 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-29select 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 | +-+-+ | 2013-04-08 21:03:14 | 30 | +-+-- + 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 dayselect date_add (@ dt, interval 1 hour);-add 1 hourselect 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 12 bloc 1233 mistaking MySQL > mysql > select date_add (@ dt, interval' 01 rig 15lo 30' hour_second) +-+ | date_add (@ dt, interval'01 hour_second 15 hour_second) | +-+ | 2008-08-09 13:28:03 | +-+ mysql > select date_add (@ dt, interval '101) +-+ | date_add (@ dt, interval '01interval' 01interval '0115 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 1V); +-+ | date_sub ('1998-01-01 00 day_second, interval' 1 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 (20080808Laure 2) +-+ | period_add (200808jue 2) | period_add (20080808lement2) | +-+ | 200810 | 20080806 | +-+ MySQL period_diff (P1Magol P2): date P1-P2, N months are returned. 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');-7select datediff ('2008-08-01,' 2008-08-08);-- 7MySQL 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:08select timediff (/

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

MySQL date conversion function, time conversion function

1. MySQL (time, second) conversion function: time_to_sec (time), sec_to_time (seconds)

Select time_to_sec ('01VOLARO 05');-3605select sec_to_time (3605); -' 01VOLLARO 05'

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

Select to_days ('0000-00');-0select 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 ('08Universe 2008,'% m);-2008-08-09select str_to_date ('08max, 09max,'% m);-2008-08-09select str_to_date ('08.09.2008,'% m.% d.% Y');-2008-08-09select str_to_date ('08lv 09mm,'% h% iRose% s') -08:09:30select str_to_date ('08.09.2008 08 08:09:30select str_to_date 09,'% m.%d.%Y% HRV% 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 mysql 2300mm,'% W% M% Y') | +-+ | Friday August 2008 | +-+ mysql > select date_format ('2008-08-08 22mysql 231mm,'% Y% m% d% H% I% s') +-+ | date_format ('2008-08-0822: 23select time_format 01bike,'% Y% m% d% H% I% s') | +-+ | 20080808222301 | +-+ mysql > select time_format ('2223Freight 01percent,'% H.% i.% s') +-+ | time_format ('22 _ 22 _

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'); -'% 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 uses fewer opportunities in practice.

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

Select makedate (2001, select makedate 31);-'2001-01-31'select makedate (2001, 32); -' 2001-02-01'select maketime (12, 15, 15, 30);-'12:15:30'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 ();-1218290027select unix_timestamp ('2008-08-08');-1218124800select unix_timestamp ('2008-08-08 12 1218124800select unix_timestamp 3030 1218124800select unix_timestamp');-1218169800select 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'

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

Timestamp (date)-date to timestamptimestamp (dt,time)-dt + timetimestampadd (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:00select timestamp (day, 1, '2008-08-08-08-08 09:01:01select timestampadd (day, 1,' 2008-08-08-08-08-08));-2008-08-08 09:01:01select timestamp ('2008-08-08-08-08) -2008-08-09 08:00:00select date_add ('2008-08-08 08:00:00select date_add, interval 1 day); the-2008-08-09 08:00:00MySQL timestampadd () function is similar to date_add (). Select timestampdiff (year,'2002-05-01);-- 1select timestampdiff (day, '2002-05-01);-- 485select timestampdiff (hour,'2008-08-08 12);-- 485select timestampdiff (hour,'2008-08-08 12);-12select datediff (' 2008-08-08-12) The-7MySQL timestampdiff () function is much more powerful than datediff (), and datediff () can only calculate the number of days between two dates (date).

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

The MySQL time zone (timezone) conversion function convert_tz (dt,from_tz,to_tz) select convert_tz ('2008-08-08 12 purge 00-00,' + 08-08-04:00:00 time zone conversion can also be realized by date_add, date_sub, timestampadd. Select date_add ('2008-08-08 12 hour hour, interval-8);-2008-08-08 04:00:00select date_sub (' 2008-08-08 12 04:00:00select date_sub, interval 8 hour);-2008-08-08 04:00:00select timestampadd (hour,-8, '2008-08-08 12 hour') -2008-08-08 04:00:00 "how to get the current time and timestamp by MySQL", that's all. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.