SELECT
-- 自然日
naturalday,
-- 财年
concat('FY', DATE_FORMAT(naturalday,'%y'), DATE_FORMAT(naturalday,'%y')+1) as fiscalyear,
-- 财季
concat('Q', case when QUARTER(naturalday)=4 then 1 ELSE QUARTER(naturalday)-1 END
) as fiscalquarter,
-- 财月
DATE_FORMAT(naturalday,'%m') as fiscalmonth,
-- 财周
timestampdiff(WEEK,date(concat(year(naturalday),'-',elt(quarter(naturalday),1,4,7,10),'-',1)),naturalday) as fiscalweek
from tool_lenovo_day
一下是我写的一个存储过程:
CREATE DEFINER=`root`@`%` PROCEDURE `sp_InitialFiscalYear_data`(`sDate` varchar(20),`eDate` varchar(20))
BEGIN
/*
工具函数:生成时间段内的lenovo的财年、财季、财月、财周数据
做成:白纸黑字
做成日:2020-07-16
入参:
sDate 开始自然日
eDate 结束自然日
业务逻辑:
tool_lenovo_day 财年表
有更新、没有新增财年数据
*/
INSERT INTO tool_lenovo_day SELECT
*
FROM
(
SELECT
naturalday,
concat( 'FY', DATE_FORMAT( naturalday, '%y' ), DATE_FORMAT( naturalday, '%y' )+ 1 ) AS fiscalyear,
concat( 'Q', CASE WHEN QUARTER ( naturalday )= 4 THEN 1 ELSE QUARTER ( naturalday )- 1 END ) AS fiscalquarter,
DATE_FORMAT( naturalday, '%m' ) AS fiscalmonth,
timestampdiff(
WEEK,
date(
concat( YEAR ( naturalday ), '-', elt( QUARTER ( naturalday ), 1, 4, 7, 10 ), '-', 1 )),
naturalday
)+1 AS fiscalweek
FROM
(
SELECT
date_add( date_sub(sDate,interval 1 day), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) day ) naturalday
FROM
mysql.help_topic
WHERE
help_topic_id <DATEDIFF(eDate,date_sub(sDate,interval 1 day))
ORDER BY
help_topic_id
) as s_e_day
) AS a
ON DUPLICATE KEY UPDATE fiscalyear = a.fiscalyear,
fiscalquarter = a.fiscalquarter,
fiscalmonth = a.fiscalmonth,
fiscalweek = a.fiscalweek
END
将Excel文件中的数据保存到mysql中时,发现日期变成了数字。当excel中的日期以数字显示就会变成类似44721.81这样的数字,这是从1900年开始的天数。
而UNIX时间戳的 0 按照 ISO 8601 规范为 :1970-01-01T00:00:00Z.。也就是从1970开始。
那当我们需要在其他地方处理excel这种时间时就只需要加上70年就行了。接下来的问题就是如何知道这70年的秒数或毫秒数,可以在excel输入1970/1/1然后转换成数字得到25569,这是天数,秒则为25569*24*60*60=2209161600。毫秒的话再乘1000。精度取到秒和毫秒按实际情况定。
在excel中有个日期2022/6/9 19:31:57.850,当其为数字格式时为44721.813864,就叫他Excel时间。
如果需要在java中将其转化为日期,Date date = new Date(new Long(时间戳)这里需要毫秒精度的时间戳。先将Excel时间*一天的毫秒数(24*60*60*1000)-70年(2209161600*1000)-8小时时差(8*60*60*1000)=1654774317850。代入上方得到2022/6/9 19:31:57.850,转换正确。
如果需要在mysql中将其转化为日期,FROM_UNIXTIME(时间戳,'%Y-%m-%d %H:%i:%S')这里只需要秒精度的时间戳。先将Excel时间*一天的秒数(24*60*60)-70年(2209161600)-8小时时差(8*60*60)=1654774317。代入上方得到2022/6/9 19:31:57,转换正确。
当excel中的日期到mysql中变成数字了,只需要FROM_UNIXTIME(Excel时间*86400-2209190400,'%Y-%m-%d %H:%i:%s')就可以转化成正常时间了
select curDate()#获取当前日期select curTime()#获取当前时间select now()#获取当前日期+时间列举1个天数加减的例子,其他的看英文意思就可以理解了
select date_add(now(), interval 1 day) #当前日期天数+1
select date_add(now(), interval -1 day) #当前日期天数-1
select date_add(now(), interval 1 hour)
select date_add(now(), interval 1 minute)
select date_add(now(), interval 1 second)
select date_add(now(), interval 1 microsecond)
select date_add(now(), interval 1 week)
select date_add(now(), interval 1 month)
select date_add(now(), interval 1 quarter)
select date_add(now(), interval 1 year)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)