sql的时间格式

时间：2023-03-09 16:36:08
sql的时间格式

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;

sql的时间格式

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

sql的时间格式

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.****.net/shenliang1985/article/details/90142010

相关文章