sql的date、时间函数、时间戳

sql中的时间格式转换主要有：date_format函数，str_to_date函数

1. 首先选择一个数据库

use db_name;

2. 显示当前时区的时间：

SELECT NOW();

3. 按照格式显示，使用 date_format 函数：

select date_format(NOW(),'%W-%Y-%m-%d') as column_name;
select date_format(NOW(),'%W-%Y-%m-%d') column_name;

 

 4. 格式化代码

%a	Short weekday name in current locale (Variable lc_time_names).
%b	Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c	Month with 1 or 2 digits.
%D	Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d	Day with 2 digits.
%e	Day with 1 or 2 digits.
%f	Sub seconds 6 digits.
%H	Hour with 2 digits between 00-23.
%h	Hour with 2 digits between 01-12.
%I	Hour with 2 digits between 01-12.
%i	Minute with 2 digits.
%j	Day of the year (001-366)
%k	Hour with 1 digits between 0-23.
%l	Hour with 1 digits between 1-12.
%M	Full month name in current locale (Variable lc_time_names).
%m	Month with 2 digits.
%p	AM/PM according to current locale (Variable lc_time_names).
%r	Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S	Seconds with 2 digits.
%s	Seconds with 2 digits.
%T	Time in 24 hour format. Short for '%H:%i:%S'.
%U	Week number (00-53), when first day of the week is Sunday.
%u	Week number (00-53), when first day of the week is Monday.
%V	Week number (01-53), when first day of the week is Sunday. Used with %X.
%v	Week number (01-53), when first day of the week is Monday. Used with %x.
%W	Full weekday name in current locale (Variable lc_time_names).
%w	Day of the week. 0 = Sunday, 6 = Saturday.
%X	Year with 4 digits when first day of the week is Sunday. Used with %V.
%x	Year with 4 digits when first day of the week is Monday. Used with %v.
%Y	Year with 4 digits.
%y	Year with 2 digits.
%#	For str_to_date(), skip all numbers.
%.	For str_to_date(), skip all punctation characters.
%@	For str_to_date(), skip all alpha characters.
%%	A literal % character.

5. 显示当前区域编码，因为时间与当地时间（区域有关）

SELECT @@lc_time_names loc_name; #loc_name为显示的列名
SET lc_time_names = 'zh_CN'; #设置区域编码

6. 先显示表格 felix_test

CREATE TABLE IF NOT EXISTS felix_test(
   id INT UNSIGNED AUTO_INCREMENT,
   author VARCHAR(40) NOT NULL,
   submission_date DATE,
     submission_time DATETIME,
   PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. 获取从0年（0000-00-00）开始的天数 —— to_days()，常用于获取昨天、前几天、一周内、一月内等的数据

select TO_DAYS(submission_time) from felix_test; 
select TO_DAYS(submission_date) from felix_test; 

以下四种形式的数据得到的to_days结果一致

select TO_DAYS(20200325); 
select TO_DAYS(200325); 
select TO_DAYS('2020-03-25')
select TO_DAYS('20-03-25')

求刚好是一周前的数据和大于一周的数据：

select * from felix_test where (TO_DAYS(now())-TO_DAYS(submission_time))=7; 
select * from felix_test where (TO_DAYS(now())-TO_DAYS(submission_time))>7; 

8. 相应的，获取从0年开始的秒数 —— to_seconds()

select TO_SECONDS(submission_time) from felix_test; 
select TO_SECONDS(submission_date) from felix_test; 

9. 分别获取时间的年、月、日、时、分、秒 —— year()，month()，day()，hour()，minute()，second()

select year(submission_time) from felix_test;
select month(submission_time) from felix_test;
select day(submission_time) from felix_test;
select hour(submission_time) from felix_test;
select minute(submission_time) from felix_test;
select second(submission_time) from felix_test;

10. 日期转时间戳 —— hive与mysql的 unix_timestamp 使用一致

select unix_timestamp(submission_time) from felix_test;  #日期时间转时间戳，此函数hive与此一致

11. 时间戳转日期（mysql）

select from_unixtime(1572316836 ,'%Y/%m/%d %H:%i:%s');  #时间戳转日期
select from_unixtime(1572316836,'%Y/%m/%d %H:%i:%s') from felix_test;  #时间戳转日期，其中的时间戳可以换成值为时间戳的列名

12. 时间戳转日期（hive）

select from_unixtime(cast(1572316836 as bigint),'yyyy/MM/dd HH:mm:ss')
select from_unixtime(cast(createtime/1000 as bigint),'yyyy/MM/dd HH:mm:ss') from db_name.tb_name limit 10
# create/1000 表示时间戳此时单位是毫秒，如果是秒，就不用了除以1000

参考：

https://www.cnblogs.com/wjm956/p/7297942.html

https://blog.csdn.net/shenliang1985/article/details/90142010