MySQL MySQL从毫秒字段中选择格式化日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18176088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select formatted date from millisecond field
提问by Jason C
I have a column in a MySQL database that contains a date as milliseconds (epoch). I want to build an SQL query that formats the date as something human readable (day, month, year, hours, minutes, seconds in any format and time zone). Is there an SQL (or MySQL-specific) function to do this?
我在 MySQL 数据库中有一个列，其中包含一个以毫秒为单位的日期（纪元）。我想构建一个 SQL 查询，将日期格式化为人类可读的内容（任何格式和时区的日、月、年、小时、分钟、秒）。是否有 SQL（或特定于 MySQL 的）函数来执行此操作？
回答by woofmeow
Try using the FROM_UNIXTIMEfunction like this as given in the manual
尝试使用手册中FROM_UNIXTIME给出的功能
SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19'
You could also use formatting like this
你也可以使用这样的格式
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2007 30th November 10:30:59 2007'
回答by AbD_Riz
If you want to get the microsecond from a field, use %f,
如果要从字段中获取微秒，请使用 %f，
select FROM_UNIXTIME(DATE_COLUMN/1000,'%Y-%M-%d %H:%i:%s %f') from TABLE_NAME;
+-------------------------------------------------------+
| FROM_UNIXTIME(CREATETIME/1000,'%Y-%M-%d %H:%i:%s %f') |
+-------------------------------------------------------+
| 2016-March-18 16:02:54 342000 |
+-------------------------------------------------------+
Source : MYSQL DATE_FORMAT
回答by hardika
CREATE DEFINER=`root`@`localhost` FUNCTION `ConvertTimeDelta`(duration int) RETURNS
varchar(20) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE time_delta VARCHAR(20);
DECLARE date_format VARCHAR(20);
SET date_format = IF (duration > 3599999, '%H:%i:%s', '%i:%s');
SET time_delta = TIME_FORMAT(SEC_TO_TIME(duration/1000), date_format);
RETURN time_delta;
END
For example select ConvertTimeDelta(4800240) result = 01:20:00 select ConvertTimeDelta(35076) result = 00:35
例如选择 ConvertTimeDelta(4800240) 结果 = 01:20:00 选择 ConvertTimeDelta(35076) 结果 = 00:35
