Mysql date function _ MySQL

Mysql date function bitsCN.com does not say anything, first record an 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 '2017-01-19 00:00:00 'and '2017-01-19 01:00:00'
Then start to convert .....
001
MySQL date and time functions
002

003
DAYOFWEEK (date)
004
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC standard)
005
Mysql> select DAYOFWEEK ('2017-02-03 ');
006
-> 3
007
WEEKDAY (date)
008
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
009
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
010
-> 5
011
Mysql> select WEEKDAY ('2017-11-05 ');
012
-> 2
013
DAYOFMONTH (date)
014
Returns the day (within the range of 1 to 31) from January 1, January)
015
Mysql> select DAYOFMONTH ('2017-02-03 ');
016
-> 3
017
DAYOFYEAR (date)
018
Returns the day (within the range of 1 to 366) of a year)
019
Mysql> select DAYOFYEAR ('2017-02-03 ');
020
-> 34
021
MONTH (date)
022
Returns the month value in date.
023
Mysql> select MONTH ('2014-02-03 ');
024
-> 2
025
DAYNAME (date)
026
Returns the day of the week by the English name)
027
Mysql> select DAYNAME ("1998-02-05 ");
028
-> 'Thursday'
029
MONTHNAME (date)
030
Returns the month of the date value (returned by English name)
031
Mysql> select MONTHNAME ("1998-02-05 ");
032
-> 'February'
033
QUARTER (date)
034
Returns the quarter of the year for date.
035
Mysql> select QUARTER ('98-04-01 ');
036
-> 2
037
WEEK (date, first)
038
Returns the week number of the year for date. (the default value of first is 0. The value of first is 1, indicating that Monday is the start of the week. 0 starts from Sunday)
039
Mysql> select WEEK ('2017-02-20 ');
040
-> 7
041
Mysql> select WEEK ('2017-02-20 ', 0 );
042
-> 7
043
Mysql> select WEEK ('2017-02-20 ', 1 );
044
-> 8
045
YEAR (date)
046
Returns the year of date (range: 1000 to 9999)
047
Mysql> select YEAR ('98-02-03 ');
048
-> 1998
049
HOUR (time)
050
Returns the hour of time (ranging from 0 to 23)
051
Mysql> select HOUR ('10: 05: 03 ');
052
-> 10
053
MINUTE (time)
054
Returns the number of minutes of time (ranging from 0 to 59)
055
Mysql> select MINUTE ('98-02-03 10:05:03 ');
056
-> 5
057
SECOND (time)
058
Returns the number of seconds (ranging from 0 to 59) of time)
059
Mysql> select SECOND ('10: 05: 03 ');
060
-> 3
061
PERIOD_ADD (P, N)
062
Add N months to period P and return (P format: YYMM or YYYYMM)
063
Mysql> select PERIOD_ADD (9801,2 );
064
-> 199803
065
PERIOD_DIFF (P1, P2)
066
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for 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
Addition and subtraction of date and time
074
(ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB (). You can also use operators and-instead of functions.
075
Date is a DATETIME or DATE value. an expression string type used for the addition and subtraction of date by expr indicates how the expression expr should be interpreted.
076
[Expected expr format]:
077
SECOND SECONDS
078
MINUTE MINUTES
079
HOUR time HOURS
080
DAY DAYS
081
MONTH-MONTH MONTHS
082
YEAR YEARS
083
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
084
HOUR_MINUTE hour and minute "HOURS: MINUTES"
085
DAY_HOUR and hour "days hours"
086
YEAR_MONTH and month "YEARS-MONTHS"
087
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
088
DAY_MINUTE day, hour, minute "days hours: MINUTES"
089
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"
090
Expr allows any punctuation to be used as a separator. If all values are DATE values, the result is a DATE value; otherwise, the result is a DATETIME value)
091
If the type keyword is incomplete, MySQL will take the value from the right end. DAY_SECOND is equal to MINUTE_SECOND because the hour is missing)
092
If you increase MONTH, YEAR_MONTH, or YEAR, the number of days is greater than the maximum number of days in the result MONTH)
093
Mysql> SELECT "23:59:59" INTERVAL 1 SECOND;
094
-> 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 ("23:59:59", INTERVAL 1 SECOND );
100
-> 00:00:00
101
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1 DAY );
102
-> 23:59:59
103
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL "" MINUTE_SECOND );
104
-> 00:01:00
105
Mysql> SELECT DATE_SUB ("00:00:00", INTERVAL "1" DAY_SECOND );
106
-> 1997-12-30 22:58:59
107
Mysql> SELECT DATE_ADD ("00:00:00", INTERVAL "-1 10" DAY_HOUR );
108
-> 1997-12-30 14:00:00
109
Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
110
-> 1997-12-02
111
Mysql> select extract (year from "maid ");
112
-> 1999
113
Mysql> select extract (YEAR_MONTH FROM "01:02:03 ");
114
-> 199907
115
Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
116
-> 20102
117
TO_DAYS (date)
118
Returns the number of days since the date of the date (not calculated before January 1, 1582)
119
Mysql> select TO_DAYS (950501 );
120
-> 728779
121
Mysql> select TO_DAYS ('2017-10-07 ');
122
-> 729669
123
FROM_DAYS (N)
124
Returns the DATE value for the day from the DATE of the West dollar (not counted before January 1, 1582)
125
Mysql> select FROM_DAYS (729669 );
126
-> '2017-10-07'
127
DATE_FORMAT (date, format)
128
Format the date value based on the format string
129
(The flag is available in the format string:
130
% M month name (January ...... December)
131
% W name of the week (Sunday ...... Saturday)
132
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
133
% Y year, number, 4 digits
134
% Y year, number, 2 digits
135
% A abbreviated name of the week (Sun ...... Sat)
136
% D number of days in the month (00 ...... 31)
137
% E number of days in the month (0 ...... 31)
138
% M month, number (01 ...... 12)
139
% C month, number (1 ...... 12)
140
% B abbreviated month name (Jan ...... Dec)
141
% J days in a year (001 ...... 366)
142
% H Hour (00 ...... 23)
143
% K hour (0 ...... 23)
144
% H Hour (01 ...... 12)
145
% I hour (01 ...... 12)
146
% L hour (1 ...... 12)
147
% I minute, number (00 ...... 59)
148
% R time, 12 hours (hh: mm: ss [AP] M)
149
% T time, 24 hours (hh: mm: ss)
150
% S seconds (00 ...... 59)
151
% S seconds (00 ...... 59)
152
% P AM or PM
153
% W days in a week (0 = Sunday ...... 6 = Saturday)
154
% U week (0 ...... 52). Sunday is the first day of the week.
155
% U week (0 ...... 52) Monday is the first day of the week.
156
% Characters %)
157
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % Y ');
158
-> 'Saturday October 1997'
159
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % S ');
160
-> '22: 23: 00'
161
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y % a % d % m % B % J ');
162
-> '4th 97 Sat 04 10 Oct 123'
163
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I % r % T % S % W ');
164
-> '22 22 10 10:23:00 PM 22:23:00 6'
165
TIME_FORMAT (time, format)
166
Similar to DATE_FORMAT (), but TIME_FORMAT only processes hours, minutes, and seconds (other symbols generate a NULL value or 0)
167
CURDATE ()
168
CURRENT_DATE ()
169
Returns the current date value in 'yyyy-MM-DD 'or YYYYMMDD format (depending on the context in which the returned value is a string or number)
170
Mysql> select CURDATE ();
171
-> '2017-12-15'
172
Mysql> select CURDATE () 0;
173
-> 19971215
174
CURTIME ()
175
CURRENT_TIME ()
176
Returns the current time value in 'hh: MM: SS' or HHMMSS format (based on the context in which the returned value is a string or number)
177
Mysql> select CURTIME ();
178
-> '23: 50: 26'
179
Mysql> select CURTIME () 0;
180
-> 235026
181
NOW ()
182
SYSDATE ()
183
CURRENT_TIMESTAMP ()
184
Returns the current date time in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format (depending on the context in which the returned value is a string or number)
185
Mysql> select NOW ();
186
-> '2017-12-15 23:50:26'
187
Mysql> select NOW () 0;
188
-> 19971215235026
189
UNIX_TIMESTAMP ()
190
UNIX_TIMESTAMP (date)
191
Returns a Unix timestamp (the number of seconds since '2017-01-01 00:00:00 'GMT, and the default value of date is the current time)
192
Mysql> select UNIX_TIMESTAMP ();
193
-> 882226357
194
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
195
-> 875996580
196
FROM_UNIXTIME (unix_timestamp)
197
Returns the timestamp value in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format (depending on the context in which the returned value is a string or number)
198
Mysql> select FROM_UNIXTIME (875996580 );
199
-> '2017-10-04 22:23:00'
200
Mysql> select FROM_UNIXTIME (875996580) 0;
201
-> 19971004222300
202
FROM_UNIXTIME (unix_timestamp, format)
203
Returns the timestamp value in format.
204
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M % h: % I: % s % X ');
205
-> '2014 23rd December 03:43:30 x'
206
SEC_TO_TIME (seconds)
207
Returns the TIME value in the format of 'hh: MM: SS' or HHMMSS in seconds (depending on the context of the returned value, it is a string or number)
208
Mysql> select SEC_TO_TIME (2378 );
209
-> '00: 39: 38'
210
Mysql> select SEC_TO_TIME (2378) 0;
211
-> 3938
212
TIME_TO_SEC (time)
213
How many seconds does the returned time value have?
214
Mysql> select TIME_TO_SEC ('22: 23: 00 ');
215
-> 80580
216
Mysql> select TIME_TO_SEC ('00: 39: 38 ');
217
-> 2378


From OO & XX's blog bitsCN.com