Date formatting for different databases

If you want to convert the dates in different databases into different types, you can use the following method: the data in the database is stored in the form of System. currentTimeMillis. MySQL: Date_format (FROM_UNIXTIME (create_time1000), % Y-% m); convert to FROM_UNIXTIME (create_time

If you want to convert the dates in different databases into different types, you can use the following method: the data in the database is stored in the form of System. currentTimeMillis. MySQL: Date_format (FROM_UNIXTIME (create_time/1000), '% Y-% m'); // convert it to FROM_UNIXTIME (create_time/

If you want to convert the dates in different databases into different formats, refer to the following practices:

The data in the database is stored as System. currentTimeMillis.

MySQL: Date_format (FROM_UNIXTIME (create_time/1000), '% Y-% m'); // convert to 2014-11 format

FROM_UNIXTIME (create_time/1000) is converted to the format 03:45:03, FROM_UNIXTIME (seds), and seds is generally 10 digits.

The meaning of each character in the formatting string is as follows:

% M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D Date of the month with English suffix (1st, 2nd, 3rd ...)
% 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 "% ".

SQLServer: substring (convert (varchar (10), dateadd (ss, create_time/1000 + 28800, '2017-01-01 '), 1970), 120 ); // The format is 2014-11

The DATEADD () function adds or removes the specified time interval in the date.

Specific reference: http://www.w3school.com.cn/ SQL /func_dateadd.asp

The CONVERT () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

Specific reference: http://www.w3school.com.cn/ SQL /func_convert.asp

Oracle: to_char (TO_DATE ('20170101', 'mmddyyyy') + 01011970/1/24 * (create_time/60/60), 'yyyy-mm ');

TO_DATE (datechar, format) converts a datechar string to the date format specified by format.

TO_CHAR (date, format) converts a date to a date of the string type specified by format.

Unixtime indicates the number of seconds that have elapsed since 00:00:00, January 1, January 1, 1970.