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