MySQL Development basic Series 6 numeric and date functions

I. Numeric functions

　　

1. ABS (x) returns the absolute value of X

SELECT ABS (-0.8),ABS(0.8);

2.ceil (x) returns the largest integer greater than X

SELECT Ceil (-0.8), Ceil (0.8);

3.floor (x) returns the largest integer less than x

SELECT  Floor (-0.8),floor (0.8);

4.mod (x, y) returns the modulo of X/y

SELECT MoD (+), mod (1, one), mod (NULL,ten)

5. Rand () returns a random value from 0 to 1

SELECT RAND (),RAND();

Also a random number of the specified range, such as the value of 0~100

SELECT Ceil (*RAND());

6.round (x, y) returns the rounding of parameter x with a Y decimal value, or 0 if y is not written

SELECT ROUND (1.1),ROUND(1.1,2),ROUND(1.0,3)

7. Truncate (x, y) returns the result of the number x truncated to Y decimal places to see the difference from the round

SELECT ROUND (1.235,2),TRUNCATE(1.235,2);

Two. Date and time functions

1. Curdate () returns the current date, only the month and day

SELECT Curdate ();

2. Curtime (): Returns the current time and seconds

SELECT Curtime ();

3.now () Return month day seconds

SELECT Now ();

4. Unix_timestamp (date) returns UNIX time truncation

  SELECT Unix_timestamp (now ())

5. From_unixtime (unixtime) and Unix_timestamp

SELECT From_unixtime (1530265708)

6. Week (date) and year (date), returns the week ordinal of the year

SELECT WEEK (now ()),Year (now ());

7.hour (time) and minute (time) return hours and minutes

  SELECT HOUR (Curtime ()), MINUTE (Curtime ());

8. MonthName (date) return time English month

SELECT  MONTHNAME (now ());

9. Date_format (DATE,FMT) displays the date in the specified format

The following example shows the current time as "month, day, year" format

SELECT  Date_format (now (),'%m,%d,%y')

Date_add (date,interval expr type) returns the date interval time period from the given date

　　

The 1th column of the following example returns the current datetime, 2 columns returns the DateTime after 31 days from the current date, and 3 columns returns the DateTime after two months from the current date. (You can also use a negative number to indicate the date time of the rewind)

SELECT  as  Current ,      to  Day  as After31dyas,     ' 1_2 ' Year_month) as   after_oneyear_twomonth;

DateDiff (DATE1,DATE2) is used to calculate the number of days between two dates

SELECT DATEDIFF ('2018-08-08', now ())    

MySQL Development basic Series 6 numeric and date functions