Conversion between the specified format of millisecond value and date in MySQL and its time function _ MySQL

Conversion of the specified format of the millisecond value and date in MySQL and its time function bitsCN.com I. conversion of the millisecond value to the specified date format

Use the FROM_UNIXTIME (unix_timestamp, format) function provided by MYSQL ).

Example: select FROM_UNIXTIME (1364176514656/1000, '% Y-% m-% d % h: % I: % S') as date; result: 09:55:15
Here, unix_timestamp is the field value/1000. format. The value that can be used is % M month name (January ...... December) % W name of the week (Sunday ...... Saturday) % D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .) % Y year, number, 4 digits % y year, number, 2 digits % A abbreviated name of the week (Sun ...... Sat) % D number of days in the month (00 ...... 31) % E number of days in the month (0 ...... 31) % M month, number (01 ...... 12) % C month, number (1 ...... 12) % B abbreviated month name (Jan ...... Dec) % J days in a year (001 ...... 366) % H Hour (00 ...... 23) % K hour (0 ...... 23) % H Hour (01 ...... 12) % I hour (01 ...... 12) % L hour (1 ...... 12) % I minute, 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 days in a week (0 = Sunday ...... 6 = Saturday) % U week (0 ...... 52). Sunday is the first day of the week. % U week (0 ...... 52) Monday is the first day of the week. % A text "% ".

II. convert the date format to the millisecond value

Similarly, sometimes we want to convert datetime data to int type and save it to the database. In this case, the select UNIX_TIMESTAMP ('2017-05-31 23:59:59 ') function is used. // This is only a second value. if the millisecond value is required, multiply it by 2011.
Result 1306771200: note that the UNIX_TIMESTAMP function returns only 1970 .... The current number of seconds. If the previous function is the same as the number of milliseconds, you need to multiply it by 1000.

3. convert to the specified date format based on the date format

DATE_FORMAT (date, format)The function can format the date or date and time value based on the format string to return the result string.
You can also use DATE_FORMAT () to format the DATE or DATETIME Value to obtain the expected format. Format the date value based on the format string:

The following describes the function parameters:
% S, % s two-digit second (,..., 59)
% I two-digit score (,..., 59)
% H two-digit hour, 24 hours (,..., 23)
% H, % I two-digit hour, 12 hours (,..., 12)
% K, 24 hours (0, 1,..., 23)
% L hour in numeric format (1, 2,..., 12)
% T 24-hour time format (hh: mm: s)
% R 12-hour time format (hh: mm: ss AM or hh: mm: ss PM)
% P AM or P M
% W name of each day in a week (Sunday, Monday,..., Saturday)
% A abbreviation of the name of each day in a week (Sun, Mon,..., Sat)
% D indicates the number of days in a month (00, 01,..., 31)
% E indicates the number of days in a month (1, 2,..., 31)
% D indicates the number of days in a month (1st, 2nd, 3rd ,...)
% W represents the number of days in a week (0 = Sunday, 1 = Monday,..., 6 = Saturday)
% J represents the number of days in a year in three digits (001,002,..., 366)
% U week (0, 1, 52), where Sunday is the first day of the week
% U week (0, 1, 52), where Monday is the first day of the week
% M month name (January, February,..., December)
% B abbreviated month name (January, February,..., December)
% M double-digit month (01, 02,..., 12)
% C indicates the month (1, 2,..., 12)
The year in the format of % Y.
The year in double-digit % y format.
% Direct value "%"

Example:
Select date_format (date field, '% Y-% m-% d') as 'date' from test
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % Y ');
-> 'Saturday October 1997'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % S ');
-> '22: 23: 00'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',
'% D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',
'% H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'
Mysql> SELECT DATE_FORMAT ('2017-01-01 ',' % X % V ');
-> '2014 52'
In MySQL 3.23, the character '%' is required before the format modifier '. In earlier MySQL versions, '%' is optional. The reason that the range of the month and day modifier starts from scratch is that in MySQL 3.23, it allows the storage of imperfect date values (for example, '2017-00-00 ').

4. Other time functions

DAYOFWEEK (date)
Returns the index of the week of the date (1 = Sunday, 2 = Monday ,...... 7 = Saturday ). These index values correspond to the ODBC standard.
Mysql> select DAYOFWEEK ('2017-02-03 ');
-> 3
WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
-> 5
Mysql> select WEEKDAY ('2017-11-05 ');
-> 2
DAYOFMONTH (date)
Returns the date of a month in the range of 1 to 31.
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3
DAYOFYEAR (date)
Returns the number of days in a year from 1 to 366.
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34
MONTH (date)
Returns the month of date, ranging from 1 to 12.
Mysql> select MONTH ('2014-02-03 ');
-> 2
DAYNAME (date)
Returns the week name of date.
Mysql> select DAYNAME ("1998-02-05 ");
-> 'Thursday'
MONTHNAME (date)
Returns the month name of date.
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'
QUARTER (date)
Returns the quarter of a year from date, ranging from 1 to 4.
Mysql> select QUARTER ('98-04-01 ');
-> 2
WEEK (date)
　
WEEK (date, first)
If Sunday is the first day of a week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. Two parameter formats: WEEK ()
Specify whether the week starts on Sunday or Monday. If the second parameter is 0 and the week starts from Sunday, if the second parameter is 1,
Starting from Monday.
Mysql> select WEEK ('2017-02-20 ');
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8
YEAR (date)
Returns the year of date, ranging from 1000 to 9999.
Mysql> select YEAR ('98-02-03 ');
-> 1998
HOUR (time)
Returns the hour of time, ranging from 0 to 23.
Mysql> select HOUR ('10: 05: 03 ');
-> 10
MINUTE (time)
Returns the minute of time, ranging from 0 to 59.
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5
SECOND (time)
The number of seconds for the return time, ranging from 0 to 59.
Mysql> select SECOND ('10: 05: 03 ');
-> 3
PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return value in the format of YYYYMM. Note that the phase parameter P is not a date value.
Mysql> select PERIOD_ADD (9801,2 );
-> 199803
PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.
Mysql> select PERIOD_DIFF (9802,199703 );
-> 11
DATE_ADD (date, INTERVAL expr type)
　
DATE_SUB (date, INTERVAL expr type)
　
ADDDATE (date, INTERVAL expr type)
　
SUBDATE (date, INTERVAL expr type)
These functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB.
In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (See the example) date is a specified start date.
DATETIME or DATE value. expr is an expression that specifies the value to be added to the start DATE or the value to be subtracted from the start DATE. expr is a string.
A "-" indicates the negative interval. Type is a keyword that specifies how the expression should be interpreted. EXTRACT (type FROM date) function FROM date
Returns the "type" interval. The following table shows how the type and expr parameters are associated: the type value indicates the expected expr format.
SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"
MySQL allows any punctuation separator in the expr format. It indicates that the recommended delimiter is displayed. If the date parameter is a DATE value and your calculation is only
Contains YEAR, MONTH, and DAY (that is, there is no time), and the result is a DATE value. Otherwise, the result is a DATETIME Value.
Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;
-> 00:00:00
Mysql> select interval 1 DAY + "1997-12-31 ";
-> 1998-01-01
Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59 ",
INTERVAL 1 SECOND );
-> 00:00:00
Mysql> SELECT DATE_ADD ("23:59:59 ",
INTERVAL 1 DAY );
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59 ",
INTERVAL "1:1" MINUTE_SECOND );
-> 00:01:00
Mysql> SELECT DATE_SUB ("00:00:00 ",
INTERVAL "1" DAY_SECOND );
-> 1997-12-30 22:58:59
Mysql> SELECT DATE_ADD ("00:00:00 ",
INTERVAL "-1 10" DAY_HOUR );
-> 1997-12-30 14:00:00
Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
-> 1997-12-02
Mysql> select extract (year from "maid ");
-> 1999
Mysql> select extract (YEAR_MONTH FROM "01:02:03 ");
-> 199907
Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
-> 20102
If you specify an interval value that is too short (excluding the expected interval of the type keyword), MySQL assumes that you have saved the leftmost portion of the interval value. For example,
If you specify a type of DAY_SECOND, the value of expr is expected to be one day, hour, minute, and second. If you specify a value like,
MySQL assumes that the days and hours are lost and the values represent minutes and seconds. In other words, "" DAY_SECOND is equivalent to "" MINUTE_SECOND
For MySQL, the TIME value represents the elapsed TIME, rather than the TIME of the day. If you use an incorrect date,
The result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the result date is greater than the maximum number of days of the new MONTH, the day is adjusted by the maximum number of days of the new moon.
Mysql> select DATE_ADD ('2017-01-30 ', Interval 1 month );
-> 1998-02-28
Note: In the previous example, the INTERVAL and type keywords are not case sensitive.
TO_DAYS (date)
Returns the number of days (from 0 years ).
Mysql> select TO_DAYS (950501 );
-> 728779
Mysql> select TO_DAYS ('2017-10-07 ');
-> 729669
TO_DAYS () is not intended to be used to use the value before the occurrence of the glipro calendars (1582.
FROM_DAYS (N)
Returns a DATE value for the number of days N.
Mysql> select FROM_DAYS (729669 );
-> '2017-10-07'
TO_DAYS () is not intended to be used to use the value before the occurrence of the glipro calendars (1582.
DATE_FORMAT (date, format)
Format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December)
% W name of the week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H Hour (00 ...... 23)
% K hour (0 ...... 23)
% H Hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, 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 days in a week (0 = Sunday ...... 6 = Saturday)
% U week (0 ...... 52). Sunday is the first day of the week.
% U week (0 ...... 52) Monday is the first day of the week.
% A text "% ".
All other characters are not interpreted and copied to the result.
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % Y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % S ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
'% D % y % a % d % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
'% H % k % I % r % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'
In MySQL3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.
TIME_FORMAT (time, format)
This is used like the preceding DATE_FORMAT () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds.
Other modifiers generate a NULL value or 0.
CURDATE ()
　
CURRENT_DATE
Returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
Mysql> select CURDATE ();
-> '2017-12-15'
Mysql> select CURDATE () + 0;
-> 19971215
CURTIME ()
　
CURRENT_TIME
Returns the current time value in 'hh: MM: SS' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select CURTIME () + 0;
-> 235026
NOW ()
　
SYSDATE ()
　
CURRENT_TIMESTAMP
Returns the current date and time in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, depending on whether the function is in a string or in a number
The context is used.
Mysql> select NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () + 0;
-> 19971215235026
UNIX_TIMESTAMP ()
　
UNIX_TIMESTAMP (date)
If no parameter is called, a Unix timestamp (in seconds starting from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () uses
The date parameter is called. it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a DATE string, a DATETIME
String, a TIMESTAMP, or a number in the local time format of YYMMDD or YYYYMMDD.
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580
When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts the value directly without the implicit "string-to-unix-timestamp" transformation.
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 being in a string
Or a digital context.
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300
FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time Mark, formatted according to the format string. Format can contain entries listed with the DATE_FORMAT () function
The same modifier.
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (),
'% Y % D % M % h: % I: % s % X ');
-> '2014 23rd December 03:43:30 x'
SEC_TO_TIME (seconds)
Returns the seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: SS' or HHMMSS, depending on whether the function is in a string or number.
Is used in the context.
Mysql> select SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select SEC_TO_TIME (2378) + 0;
-> 3938
TIME_TO_SEC (time)
Returns the time parameter, which is converted to seconds.
Mysql> select TIME_TO_SEC ('22: 23: 00 ');
-> 80580
Mysql> select TIME_TO_SEC ('00: 39: 38 ');
-> 2378

BitsCN.com