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

This article mainly explains "the usage of mysql date function". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the usage of mysql date function".

Don't say anything, just record a sql.

Select uid,rank,money,e, from_unixtime (time/1000) from stats_2012_01_19 where comm = 8888 and e > 40 and from_unixtime (time/1000) between '2012-01-19 00 and' 2012-01-19 01-19 01-19 01

Then start to turn.

001

A complete Collection of MySQL date-time functions

002

003

DAYOFWEEK (date)

004

Return date date is what day of the week (1 = Sunday, 2 = Monday,. 7 = Saturday, ODBC standard)

005

> select DAYOFWEEK ('1998-02-03')

006

-> 3

007

WEEKDAY (date)

008

Return date date is what day of the week (0 = Monday, 1 = Tuesday,. 6 = Sunday).

009

Mysql > select WEEKDAY ('1997-10-04 22 purge 23purl 00')

010

-> 5

011

Mysql > select WEEKDAY ('1997-11-05')

012

-> 2

013

DAYOFMONTH (date)

014

Returns the day of the month on which date is returned (in the range of 1 to 31)

015

Mysql > select DAYOFMONTH ('1998-02-03')

016

-> 3

017

DAYOFYEAR (date)

018

Return date is the day of the year (in the range of 1 to 366)

019

Mysql > select DAYOFYEAR ('1998-02-03')

020

-> 34

021

MONTH (date)

022

Returns the month value in date

023

Mysql > select MONTH ('1998-02-03')

024

-> 2

025

DAYNAME (date)

026

Returns what day of the week date is (returned by English name)

027

Mysql > select DAYNAME ("1998-02-05")

028

-> 'Thursday'

029

MONTHNAME (date)

030

How many months is the date returned (returned by English name)

031

Mysql > select MONTHNAME ("1998-02-05")

032

-> 'February'

033

QUARTER (date)

034

The quarter of the year in which the date is returned.

035

Mysql > select QUARTER ('98-04-01')

036

-> 2

037

WEEK (date,first)

038

Return date is the week ordinal of the year (the first default value of 0 first indicates that Monday is the beginning of the week, and 0 starts on Sunday)

039

Mysql > select WEEK ('1998-02-20')

040

-> 7

041

Mysql > select WEEK ('1998-02-20)

042

-> 7

043

Mysql > select WEEK ('1998-02-20)

044

-> 8

045

YEAR (date)

046

Returns the year of the date (range from 1000 to 9999)

047

Mysql > select YEAR ('98-02-03')

048

-> 1998

049

HOUR (time)

050

Returns the number of hours of time (range 0 to 23)

051

Mysql > select HOUR ('10 05purl 03')

052

-> 10

053

MINUTE (time)

054

Returns the number of minutes of time (range 0 to 59)

055

Mysql > select MINUTE ('98-02-03 10 purl 05purl 03')

056

-> 5

057

SECOND (time)

058

Returns the number of seconds of time (range 0 to 59)

059

Mysql > select SECOND ('10 05purl 03')

060

-> 3

061

PERIOD_ADD (PPJN)

062

Increase N months to period P and return (P format YYMM or YYYYMM)

063

Mysql > select PERIOD_ADD (9801 Magi 2)

064

-> 199803

065

PERIOD_DIFF (P1and P2)

066

Returns the number of months between periods P1 and P2 (the format YYMM or YYYYMM of P1 and P2)

067

Mysql > select PERIOD_DIFF (9802 ~ 199703)

068

-> 11

069

DATE_ADD (date,INTERVAL expr type)

070

DATE_SUB (date,INTERVAL expr type)

071

ADDDATE (date,INTERVAL expr type)

072

SUBDATE (date,INTERVAL expr type)

073

Add and subtract the date and time

074

(ADDDATE () and SUBDATE () are synonyms for DATE_ADD () and DATE_SUB (), or you can use operators and-instead of functions

075

Date is a DATETIME or date value, and expr adds or subtracts an expression string type from date that indicates how the expression expr should be interpreted

076

[type value means expected expr format]:

077

SECOND second SECONDS

078

MINUTE minute MINUTES

079

HOUR time HOURS

080

DAY days DAYS

081

MONTH monthly MONTHS

082

YEAR year YEARS

083

MINUTE_SECOND minutes and seconds "MINUTES:SECONDS"

084

HOUR_MINUTE hours and minutes "HOURS:MINUTES"

085

DAY_HOUR days and hours "DAYS HOURS"

086

YEAR_MONTH year and month "YEARS-MONTHS"

087

HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS"

088

DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES"

089

DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

090

Any punctuation separator is allowed in expr. If all are date values, the result is a date value, otherwise the result is a DATETIME value)

091

If the type keyword is incomplete, MySQL takes the value from the right, and DAY_SECOND equals MINUTE_SECOND because of the lack of hours and minutes)

092

If you increase MONTH, YEAR_MONTH, or YEAR, the maximum number of days will be used if the number of days is greater than the maximum number of days in the resulting month)

093

Mysql > SELECT "1997-12-31 23:59:59" INTERVAL 1 SECOND

094

-> 1998-01-01 00:00:00

095

Mysql > SELECT INTERVAL 1 DAY "1997-12-31"

096

-> 1998-01-01

097

Mysql > SELECT "1998-01-01"-INTERVAL 1 SECOND

098

-> 1997-12-31 23:59:59

099

Mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 SECOND)

one hundred

-> 1998-01-01 00:00:00

one hundred and one

Mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 DAY)

one hundred and two

-> 1998-01-01 23:59:59

one hundred and three

Mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND)

one hundred and four

-> 1998-01-01 00:01:00

one hundred and five

Mysql > SELECT DATE_SUB ("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND)

one hundred and six

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

one hundred and seven

Mysql > SELECT DATE_ADD ("1998-01-01 00:00:00", INTERVAL "- 1 10" DAY_HOUR)

one hundred and eight

-> 1997-12-30 14:00:00

one hundred and nine

Mysql > SELECT DATE_SUB (1998-01-02, INTERVAL 31 DAY)

one hundred and ten

-> 1997-12-02

one hundred and eleven

Mysql > SELECT EXTRACT (YEAR FROM "1999-07-02")

one hundred and twelve

-> 1999

one hundred and thirteen

Mysql > SELECT EXTRACT (YEAR_MONTH FROM "1999-07-02 01:02:03")

one hundred and fourteen

-> 199907

one hundred and fifteen

Mysql > SELECT EXTRACT (DAY_MINUTE FROM "1999-07-02 01:02:03")

one hundred and sixteen

-> 20102

one hundred and seventeen

TO_DAYS (date)

one hundred and eighteen

The return date date is the number of days since 2000 (not counting before 1582)

one hundred and nineteen

Mysql > select TO_DAYS (950501)

one hundred and twenty

-> 728779

one hundred and twenty one

Mysql > select TO_DAYS ('1997-10-07')

one hundred and twenty two

-> 729669

one hundred and twenty three

FROM_DAYS (N)

one hundred and twenty four

Give the number of days since the year 2000 to return date (not counting before 1582)

one hundred and twenty five

Mysql > select FROM_DAYS (729669)

one hundred and twenty six

-> '1997-10-07'

one hundred and twenty seven

DATE_FORMAT (date,format)

one hundred and twenty eight

Format date values according to format strings

one hundred and twenty nine

(markers are available in format strings:

one hundred and thirty

% M month name (January... December)

one hundred and thirty one

% W week name (Sunday... Saturday)

one hundred and thirty two

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

one hundred and thirty three

% Y year, number, 4 digits

one hundred and thirty four

% y year, number, 2 digits

one hundred and thirty five

% an abbreviated name of the week (Sun... Sat)

one hundred and thirty six

Number of days in% d month, number (00. 31)

one hundred and thirty seven

Number of days in% e month, number (0... 31)

one hundred and thirty eight

% m month, number (01... 12)

one hundred and thirty nine

% c month, number (1 …... 12)

one hundred and forty

% b abbreviated month name (Jan... Dec)

one hundred and forty one

% j days of the year (001... 366)

one hundred and forty two

% H hours (00. 23)

one hundred and forty three

% k hours (0. 23)

one hundred and forty four

% h hours (01 …... 12)

one hundred and forty five

% I hours (01 …... 12)

one hundred and forty six

% l hours (1 …... 12)

one hundred and forty seven

% I minutes, number (00. 59)

one hundred and forty eight

% r time, 12 hours (hh:mm:ss [AP] M)

one hundred and forty nine

% T time, 24 hours (hh:mm:ss)

one hundred and fifty

% s seconds (00. 59)

one hundred and fifty one

% s seconds (00. 59)

one hundred and fifty two

% p AM or PM

one hundred and fifty three

% w days in a week (0=Sunday... 6=Saturday)

one hundred and fifty four

% U week (0 …... Here Sunday is the first day of the week

one hundred and fifty five

% u weeks (0. Here Monday is the first day of the week

one hundred and fifty six

% character%)

one hundred and fifty seven

Mysql > select DATE_FORMAT ('1997-10-04 22 2300% M% Y')

one hundred and fifty eight

-> 'Saturday October 1997'

one hundred and fifty nine

Mysql > select DATE_FORMAT ('1997-10-04 22 2300 handkerchief% HV% iRO% s')

one hundred and sixty

->'22 2312 00'

one hundred and sixty one

Mysql > select DATE_FORMAT ('1997-10-04 22 2300% d% a% d% m% b% j')

one hundred and sixty two

-> '4th 97 Sat 04 10 Oct 277'

one hundred and sixty three

Mysql > select DATE_FORMAT ('1997-10-04 22 2300% H% k% I% r% T% S% w')

one hundred and sixty four

->'22 22 10 10:23:00 PM 22:23:00 00 6'

one hundred and sixty five

TIME_FORMAT (time,format)

one hundred and sixty six

Similar to DATE_FORMAT (), but TIME_FORMAT only handles hours, minutes, and seconds (the rest of the symbols produce a null value or 0)

one hundred and sixty seven

CURDATE ()

one hundred and sixty eight

CURRENT_DATE ()

one hundred and sixty nine

Returns the current date value in 'YYYY-MM-DD' or YYYYMMDD format (a string or number depending on the context in which the value is returned)

one hundred and seventy

Mysql > select CURDATE ()

one hundred and seventy one

-> '1997-12-15'

one hundred and seventy two

Mysql > select CURDATE () 0

one hundred and seventy three

-> 19971215

one hundred and seventy four

CURTIME ()

one hundred and seventy five

CURRENT_TIME ()

one hundred and seventy six

Returns the current time value in 'HH:MM:SS' or HHMMSS format (a string or number depending on the context in which the value is returned)

one hundred and seventy seven

Mysql > select CURTIME ()

one hundred and seventy eight

->'23 50 14 26'

one hundred and seventy nine

Mysql > select CURTIME () 0

one hundred and eighty

-> 235026

one hundred and eighty one

NOW ()

one hundred and eighty two

SYSDATE ()

one hundred and eighty three

CURRENT_TIMESTAMP ()

one hundred and eighty four

Returns the current date and time in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format (a string or number depending on the context in which the value is returned)

one hundred and eighty five

Mysql > select NOW ()

one hundred and eighty six

-> '1997-12-15 23 23 50 purl 26'

one hundred and eighty seven

Mysql > select NOW () 0

one hundred and eighty eight

-> 19971215235026

one hundred and eighty nine

UNIX_TIMESTAMP ()

one hundred and ninety

UNIX_TIMESTAMP (date)

one hundred and ninety one

Returns a Unix timestamp (seconds from '1970-01-01 00:00:00'GMT, which defaults to the current time)

one hundred and ninety two

Mysql > select UNIX_TIMESTAMP ()

one hundred and ninety three

-> 882226357

one hundred and ninety four

Mysql > select UNIX_TIMESTAMP ('1997-10-04 22 purge 23purl 00')

one hundred and ninety five

-> 875996580

one hundred and ninety six

FROM_UNIXTIME (unix_timestamp)

one hundred and ninety seven

Returns the value of the timestamp in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format (a string or number depending on the context in which the value is returned)

one hundred and ninety eight

Mysql > select FROM_UNIXTIME (875996580)

one hundred and ninety nine

-> '1997-10-04 22 22 purl 2300'

two hundred

Mysql > select FROM_UNIXTIME (875996580) 0

two hundred and one

-> 19971004222300

two hundred and two

FROM_UNIXTIME (unix_timestamp,format)

two hundred and three

Returns the value of the timestamp in format string format

two hundred and four

Mysql > select FROM_UNIXTIME (UNIX_TIMESTAMP (),'Y D M h:%i:%s x')

two hundred and five

-> '1997 23rd December 03:43:30 x'

two hundred and six

SEC_TO_TIME (seconds)

two hundred and seven

Returns the time value converted by the number of seconds in HH:MM:SS' or HHMMSS format (a string or number depending on the context in which the value is returned)

two hundred and eight

Mysql > select SEC_TO_TIME (2378)

two hundred and nine

-> '00Rose 39RU 38'

two hundred and ten

Mysql > select SEC_TO_TIME (2378) 0

two hundred and eleven

-> 3938

two hundred and twelve

TIME_TO_SEC (time)

two hundred and thirteen

Returns the number of seconds of the time value

two hundred and fourteen

Mysql > select TIME_TO_SEC ('22 2300')

two hundred and fifteen

-> 80580

two hundred and sixteen

Mysql > select TIME_TO_SEC ('00GRV 39RV 38')

two hundred and seventeen

-> 2378

Thank you for your reading, the above is the content of "the usage of mysql date function". After the study of this article, I believe you have a deeper understanding of the usage of mysql date function, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.