from_unixtime

description

Syntax

DATETIME FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])

将 unix 时间戳转化位对应的 time 格式,返回的格式由 string_format 指定

默认为 yyyy-MM-dd HH:mm:ss ,也支持date_format中的format格式

传入的是整形,返回的是字符串类型

目前 string_format 支持格式:

  1. %Y:年。例:20141900
  2. %m:月。例:1209
  3. %d:日。例:1101
  4. %H:时。例:230112
  5. %i:分。例:0511
  6. %s:秒。例:5901

其余 string_format 格式是非法的,返回NULL

如果给定的时间戳小于 0 或大于 253402271999,则返回 NULL。即时间戳范围是:

1970-01-01 00:00:00 ~ 9999-12-31 23:59:59

example

  1. mysql> select from_unixtime(1196440219);
  2. +---------------------------+
  3. | from_unixtime(1196440219) |
  4. +---------------------------+
  5. | 2007-12-01 00:30:19 |
  6. +---------------------------+
  7. mysql> select from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss');
  8. +--------------------------------------------------+
  9. | from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss') |
  10. +--------------------------------------------------+
  11. | 2007-12-01 00:30:19 |
  12. +--------------------------------------------------+
  13. mysql> select from_unixtime(1196440219, '%Y-%m-%d');
  14. +-----------------------------------------+
  15. | from_unixtime(1196440219, '%Y-%m-%d') |
  16. +-----------------------------------------+
  17. | 2007-12-01 |
  18. +-----------------------------------------+
  19. mysql> select from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s');
  20. +--------------------------------------------------+
  21. | from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s') |
  22. +--------------------------------------------------+
  23. | 2007-12-01 00:30:19 |
  24. +--------------------------------------------------+

keyword

  1. FROM_UNIXTIME,FROM,UNIXTIME