mysql时间戳及字符串时间等转换

使用mysql数据库过程中，经常会遇到时间戳与格式化字符串时间、字符串时间与date时间等的相互转换，现将转换涉及的函数和用法做下汇总，以便查阅.

一、函数列表:

1. DATE_FORMAT(date, format) : 日期格式化函数, 用于以不同的格式显示日期/时间数据; 

    date 参数是合法的日期. format 规定日期/时间的输出格式.

2. UNIX_TIMESTAMP() : 若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳;

3. UNIX_TIMESTAMP(date) : 若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字;

4. STR_TO_DATE(str,format): 将时间格式的字符串(str); 按照所提供的显示格式（format）转换为DATETIME类型的值;

5. FROM_UNIXTIME(unix_timestamp,format):  在FROM_UNIXTIME中unix_timestamp可以是字段名，也可以直接是Unix 时间戳，format主要是将返回值格式化。返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT函数列出的条目同样的修饰符。

二、使用示例:

1.  DATE_FORMAT(date, format)

  select date_format(now(),"%Y-%m-%d %T");   -- 输出:  2019-03-20 10:52:20 

2.  UNIX_TIMESTAMP()

 select UNIX_TIMESTAMP(); -- 输出: 1553050476

3.  UNIX_TIMESTAMP(date)

  select UNIX_TIMESTAMP('2019-01-06 10:20:30');  -- 输出: 1546741230
  select UNIX_TIMESTAMP(now()); -- 输出: 1553050627

  select UNIX_TIMESTAMP('2019-01-06'); -- 输出: 1546704000

4.  STR_TO_DATE(str,format)

   select STR_TO_DATE('2019-03-06 10:20:30','%Y-%m-%d %H:%i:%s'); -- 输出: 2019-03-07 00:20:30 (date类型，因数据库时区设置不同，固时间有差异)

    select STR_TO_DATE('2019-01-09', '%Y-%m-%d %T'); 输出: 2019-01-09 14:00:00 (date类型)

5. FROM_UNIXTIME(unix_timestamp,format)

    select from_unixtime(1553050627); -- 输出: 2019-03-20 23:57:07 (date类型)

    select from_unixtime(1553050627,'%Y-%m-%d %T'); -- 输出: 2019-03-20 10:57:07 (字符串)

    select from_unixtime(1553050627, '%Y-%m-%d %H:%i:%S');  -- 输出: 2019-03-20 10:57:07 (字符串)

 

三、format 样式： 

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday) 

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。