Mysql Common Date/time/numerical function detailed (must SEE) _mysql

1. Date Time function

Time Conversion second function: Time_to_sec

Mysql> Select Time_to_sec (' 01:01:01 ');
+-------------------------+
| Time_to_sec (' 01:01:01 ') |
+-------------------------+
| 3661 |
+-------------------------+
1 row in Set (0.00 sec)

Second transformation time function: Sec_to_time

Mysql> Select Sec_to_time (3661);
+-------------------+
| Sec_to_time (3661) |
+-------------------+
| 01:01:01 |
+-------------------+
1 row in Set (0.00 sec)

Date to days function: To_days

Mysql> Select To_days (' 0000-00-00 ');
+-----------------------+
| To_days (' 0000-00-00 ') |
+-----------------------+
| NULL |
+-----------------------+
1 row in Set, 1 warning (0.01 sec)

Mysql> Select To_days (' 0001-01-01 ');
+-----------------------+
| To_days (' 0001-01-01 ') |
+-----------------------+
| 366 |
+-----------------------+
1 row in Set (0.00 sec)

Days converted Date Function: From_days

Mysql> Select From_days (0);
+--------------+
| From_days (0) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in Set (0.00 sec)

Mysql> Select From_days (366);
+----------------+
| From_days (366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in Set (0.00 sec)

string conversion to date function: Str_to_date

Mysql> Select Str_to_date (' 2013-01-01 01:21:01 ', '%y-%m-%d%h:%i:%s ');
+--------------------------------------------------------+
| Str_to_date (' 2013-01-01 01:21:01 ', '%y-%m-%d%h:%i:%s ') |
+--------------------------------------------------------+
| 2013-01-01 01:21:01 |
+--------------------------------------------------------+
1 row in Set (0.00 sec)

Date converted to string function: Date_format

Mysql> Select Date_format (' 2013-01-01 01:21:01 ', '%y%m%d%h%i%s ');
+----------------------------------------------------+
| Date_format (' 2013-01-01 01:21:01 ', '%y%m%d%h%i%s ') |
+----------------------------------------------------+
| 20130101 012101 |
+----------------------------------------------------+
1 row in Set (0.00 sec)

Time conversion to string function: Time_format

Mysql> Select Time_format (' 01:21:01 ', '%h%i%s ');
+----------------------------------+
| Time_format (' 01:21:01 ', '%h%i%s ') |
+----------------------------------+
| 012101 |
+----------------------------------+
1 row in Set (0.00 sec)

Description

Date-time format parameters are as follows:

%m month name (January ...) December)
%w Week name (Sunday ...) Saturday)
%d The date of the month with English prefix (1st, 2nd, 3rd, and so on). ）
%Y years, numbers, 4-bit
%y years, numbers, 2-bit
%a name of the week (Sun ...) Sat)
Days in%d months, numbers (00 ...). 31)
%e the number of days in the month, numbers (0 ...). 31)
%m Month, number (01 ...) 12)
%c month, number (1 ...). 12)
%b abbreviated month name (...) DEC)
%j the number of days in a year (001 ...). 366)
%H hours (00 .....) 23)
%k hours (0 .....) 23)
%h hours (01 .....) 12)
%I Hours (01 .....) 12)
%l hours (1 .....) 12)
%i minutes, Number (00 .....) 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ...). 59)
%s seconds (00 ...). 59)
%p am or PM
%w the number of days in one weeks (0=sunday ...). 6=saturday)
%u weeks (0 .....) 52), here Sunday is the first day of the week
%u weeks (0 .....) 52), here Monday is the first day of the week
Percent% of a text "%".

Extract the date part of an expression

Mysql> Select Date (now ());
+-------------+
| Date (now ()) |
+-------------+
| 2013-05-16 |
+-------------+
1 row in Set (0.00 sec)

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

Mysql> Select Weekday (now ());
+----------------+
| Weekday (now ()) |
+----------------+
| 3 |
+----------------+
1 row in Set (0.00 sec)

Return expression is the week ordinal of a year

Mysql> Select Week (now ());
+-------------+
| Week (now ()) |
+-------------+
| 19 |
+-------------+
1 row in Set (0.00 sec)

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 argument is 1, starting from Monday, as follows:

Mysql> Select Week (now (), 0);
+---------------+
| Week (now (), 0) |
+---------------+
| 19 |
+---------------+
1 row in Set (0.00 sec)

Mysql> Select Week (Now (), 1);
+---------------+
| Week (Now (), 1) |
+---------------+
| 20 |
+---------------+
1 row in Set (0.00 sec)

Returns an expression for the middle of the year

Mysql> Select Quarter (now ());
+----------------+
| Quarter (now ()) |
+----------------+
| 2 |
+----------------+
1 row in Set (0.00 sec)

Returns the first day of the week of an expression

Mysql> Select DayOfWeek (now ());
+------------------+
| DayOfWeek (Now ()) |
+------------------+
| 5 |
+------------------+
1 row in Set (0.00 sec)

Returns the day ordinal of a one-month expression

Mysql> Select DayOfMonth (now ());
+-------------------+
| DayOfMonth (Now ()) |
+-------------------+
| 16 |
+-------------------+
1 row in Set (0.00 sec)

Returns the first day of the year of an expression

Mysql> Select DayOfYear (now ());
+------------------+
| DayOfYear (Now ()) |
+------------------+
| 136 |
+------------------+
1 row in Set (0.00 sec)

Returns the name of the week of an expression

Mysql> Select Dayname (now ());
+----------------+
| Dayname (Now ()) |
+----------------+
| Thursday |
+----------------+
1 row in Set (0.00 sec)

Returns the name of an expression month

Mysql> Select MonthName (now ());
+------------------+
| MonthName (Now ()) |
+------------------+
| May |
+------------------+
1 row in Set (0.00 sec)

Mysql>

Extract the year of an expression

Mysql> Select Year (now ());
+-------------+
| Year (now ()) |
+-------------+
| 2013 |
+-------------+
1 row in Set (0.00 sec)

Extract the month of an expression

Mysql> Select Month (now ());
+--------------+
| Month (now ()) |
+--------------+
| 5 |
+--------------+
1 row in Set (0.01 sec)

Number of days to extract an expression

Mysql> Select Day (now);
+------------+
| Day (now ()) |
+------------+
| 16 |
+------------+
1 row in Set (0.00 sec)

Extract the hour of an expression

Mysql> Select Hour (now ());
+-------------+
| Hour (now ()) |
+-------------+
| 16 |
+-------------+
1 row in Set (0.00 sec)

Extract the minutes of an expression

Mysql> Select Minute (now ());
+---------------+
| Minute (now ()) |
+---------------+
| 31 |
+---------------+
1 row in Set (0.00 sec)

The number of seconds to extract an expression

Mysql> Select Second (now ());
+---------------+
| Second (now ()) |
+---------------+
| 34 |
+---------------+
1 row in Set (0.00 sec)

returns the current date according to the value in ' Yyyy-mm-dd ' or YYYYMMDD format, depending on the function used in a string or a numeric context.

Mysql> select Curdate ();
+------------+
| Curdate () |
+------------+
| 2013-05-16 |
+------------+
1 row in Set (0.00 sec)

Mysql> Select Curdate () +1;
+-------------+
| Curdate () +1 |
+-------------+
| 20130517 |
+-------------+
1 row in Set (0.00 sec)

returns the current time in ' HH:MM:SS ' or HHMMSS format, depending on the function used in a string or in a digital context.

Mysql> select Curtime ();
+-----------+
| Curtime () |
+-----------+
| 16:43:10 |
+-----------+
1 row in Set (0.00 sec)

Mysql> Select Curtime () +1;
+---------------+
| Curtime () +1 |
+---------------+
| 164420.000000 |
+---------------+
1 row in Set (0.00 sec)

Get current date Time: Sysdate (), now ()

Mysql> Select Sysdate (), Sleep (2), sysdate ();
+---------------------+----------+---------------------+
| Sysdate () | Sleep (2) | Sysdate () |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:04 | 0 | 2013-05-16 17:16:06 |
+---------------------+----------+---------------------+
1 row in Set (2.00 sec)

Mysql> Select Now (), Sleep (2), now ();
+---------------------+----------+---------------------+
| Now () | Sleep (2) | Now () |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:18 | 0 | 2013-05-16 17:16:18 |
+---------------------+----------+---------------------+
1 row in Set (2.00 sec)
From the above you can see the difference between sysdate and now, where the time of the statement begins, and the Sysdate real-time acquisition time

returns the current date according to the value in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on the function used in a string or in a digital context.

Mysql> Select Current_timestamp;
+---------------------+
| Current_timestamp |
+---------------------+
| 2013-05-16 17:19:51 |
+---------------------+
1 row in Set (0.00 sec)

Mysql> Select Current_timestamp+1;
+-----------------------+
| current_timestamp+1 |
+-----------------------+
| 20130516172008.000000 |
+-----------------------+
1 row in Set (0.00 sec)

Unix_timestamp (), Unix_timestamp (date)

Returns a UNIX timestamp (the number of seconds since ' 1970-01-01 00:00:00 ' GMT) if no parameter calls are invoked. If Unix_timestamp () with a
The date parameter is called, which returns the number of seconds that start from ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime
A number that is a string, a timestamp, or a local time in YYMMDD or YYYYMMDD format.
Mysql> select Unix_timestamp ();
+------------------+
| Unix_timestamp () |
+------------------+
| 1368696216 |
+------------------+
1 row in Set (0.00 sec)

Mysql> Select Unix_timestamp (' 2013-05-16 01:01:01 ');
+---------------------------------------+
| Unix_timestamp (' 2013-05-16 01:01:01 ') |
+---------------------------------------+
| 1368637261 |
+---------------------------------------+
1 row in Set (0.00 sec)

Mysql>

From_unixtime (Unix_timestamp)

Returns the value represented by the Unix_timestamp parameter in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on the function used in a string or a numeric context

Mysql> Select From_unixtime (1368637261);
+---------------------------+
| From_unixtime (1368637261) |
+---------------------------+
| 2013-05-16 01:01:01 |
+---------------------------+
1 row in Set (0.00 sec)

Mysql> Select From_unixtime (1368637261) +1;
+-----------------------------+
| From_unixtime (1368637261) +1 |
+-----------------------------+
| 20130516010102.000000 |
+-----------------------------+
1 row in Set (0.00 sec)

Mysql> Select From_unixtime (1368637261, '%y-%m-%d%h:%i:%s ');
+-----------------------------------------------+
| From_unixtime (1368637261, '%y-%m-%d%h:%i:%s ') |
+-----------------------------------------------+
| 2013-05-16 01:01:01 |
+-----------------------------------------------+
1 row in Set (0.00 sec)

Returns the last day of the month on which an expression is located

Mysql> Select Last_day (now ());
+-----------------+
| Last_day (Now ()) |
+-----------------+
| 2013-05-31 |
+-----------------+
1 row in Set (0.00 sec)

Date addition and subtraction operation

Date_add (Date,interval expr type)--addition
Date_sub (Date,interval expr type)--subtraction

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', Interval 1 second);
+---------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', Interval 1 second) |
+---------------------------------------------------+
| 2013-05-16 01:01:02 |
+---------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', Interval 1 day);
+------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', Interval 1 day) |
+------------------------------------------------+
| 2013-05-17 01:01:01 |
+------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', interval 1 minute);
+---------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', interval 1 minute) |
+---------------------------------------------------+
| 2013-05-16 01:02:01 |
+---------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', interval 1 hour);
+-------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', Interval 1 hour) |
+-------------------------------------------------+
| 2013-05-16 02:01:01 |
+-------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', Interval ' 1:1 ' minute_second);
+--------------------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', Interval ' 1:1 ' Minute_second) |
+--------------------------------------------------------------+
| 2013-05-16 01:02:02 |
+--------------------------------------------------------------+
1 row in Set (0.00 sec)

Mysql> Select Date_add (' 2013-05-16 01:01:01 ', interval ' 1 1:1:1 ' Day_second);
+----------------------------------------------------------------+
| Date_add (' 2013-05-16 01:01:01 ', interval ' 1 1:1:1 ' Day_second) |
+----------------------------------------------------------------+
| 2013-05-17 02:02:02 |
+----------------------------------------------------------------+
1 row in Set (0.00 sec)

Type value format:

SECOND seconds SECONDS
MINUTE minutes MINUTES
HOUR Time HOURS
Day days
MONTH Month MONTHS
Year YEARS
Minute_second minute and second "Minutes:seconds"
Hour_minute hour and Minute "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month "Years-months"
Hour_second hours, minutes, "HOURS:MINUTES:SECONDS"
Day_minute day, hour, minute "Days Hours:minutes"
Day_second day, hour, minute, second "days HOURS:MINUTES:SECONDS"

DATEDIFF (expr, expr2)

Returns the number of days between the start time expr and the end time expr2. Expr and expr2 are dates or date-and-time expressions. The date part of the calculation that is used only for these values.

Mysql> Select DateDiff (' 2013-05-16 06:01:01 ', ' 2013-05-17 01:01:01 ');
+--------------------------------------------------------+
| DateDiff (' 2013-05-16 06:01:01 ', ' 2013-05-17 01:01:01 ') |
+--------------------------------------------------------+
| -1 |
+--------------------------------------------------------+
1 row in Set (0.00 sec)

Represents the date-time data type:

Date
Time
Year
Datetime
Timestamp

When using date time data comparisons are often used as follows

Mysql> select * from tab;
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
| bbbb | 2013-04-14 17:20:36 |
| bbbb | 2013-04-13 17:20:36 |
| bbbb | 2013-04-15 17:20:36 |
+------+---------------------+
4 rows in Set (0.00 sec)

Mysql> Select Now ();
+---------------------+
| Now () |
+---------------------+
| 2013-05-14 17:10:26 |
+---------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from tab where Createtime > Now ();
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in Set (0.00 sec)

Mysql> Select Current_timestamp;
+---------------------+
| Current_timestamp |
+---------------------+
| 2013-05-14 17:10:49 |
+---------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from tab where Createtime > Current_timestamp;
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from tab where createtime> str_to_date (' 2013-05-14 00:00:00 ', '%y-%m-%d%h:%i:%s ');;
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from tab where createtime between Str_to_date (' 2013-05-14 00:00:00 ', '%y-%m-%d%h:%i:%s ') and Str_to_dat E (' 2013-05-15 00:00:00 ', '%y-%m-%d%h:%i:%s ')
->;
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from tab where createtime between ' 2013-05-14 00:00:00 ' and ' 2013-05-15 00:00:00 ';
+------+---------------------+
| name | Createtime |
+------+---------------------+
| AAAA | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in Set (0.00 sec)

Mysql>

2. Numerical function

ABS (x): Returns the absolute value of an expression X

Mysql> Select ABS (-2);
+---------+
| ABS (-2) |
+---------+
| 2 |
+---------+
1 row in Set (0.00 sec)

FLOOR (x): Returns the maximum integer value not greater than X

Mysql> Select Floor (-2.45);
+--------------+
| Floor (-2.45) |
+--------------+
| -3 |
+--------------+
1 row in Set (0.00 sec)

MOD (n,m): modulo operation, returns the remainder of N being removed by M.

Mysql> Select mod (3,2);
+----------+
| MoD (3,2) |
+----------+
| 1 |
+----------+
1 row in Set (0.00 sec)

RAND ()/rand (N): Returns a random floating-point value of a, ranging from 0 to 1 (that is, its range is 0≤a≤1.0). If an integer parameter N is specified, it is used as a seed value to produce a repeating sequence.

Mysql> select rand ();
+-------------------+
| Rand () |
+-------------------+
| 0.294932589209576 |
+-------------------+
1 row in Set (0.00 sec)

Mysql> Select rand (2);
+-------------------+
| RAND (2) |
+-------------------+
| 0.655586646549019 |
+-------------------+
1 row in Set (0.00 sec)

ROUND (x)/round (X,D): Returns the parameter X, whose value is close to the nearest-like integer. In the case of two parameters, X is returned and its value is retained to the D bit after the decimal point, and the reservation of D bit is rounded. To keep the D bit to the left of the X value decimal point, you can set D to be negative.

Mysql> Select round (2.4);
+-------------+
| Round (2.4) |
+-------------+
| 2 |
+-------------+
1 row in Set (0.00 sec)

Mysql> Select round (2.432,2);
+-----------------+
| Round (2.432,2) |
+-----------------+
| 2.43 |
+-----------------+
1 row in Set (0.00 sec)

Mysql> Select round (12.432,-1);
+-------------------+
| Round (12.432,-1) |
+-------------------+
| 10 |
+-------------------+
1 row in Set (0.00 sec)

The above is small series for everyone to bring the MySQL commonly used date time/numerical function detailed (must see) all content, hope that we support cloud Habitat Community ~