问题现象:

​ 今天在项目开发中遇到了一个需求,在编写某个功能时,需要使用到基于某个时间(如当前时间),去获取本月第一天或下个月的第一天的数据.

问题分析:

通过查询资料可以得知,要实现这个需求并不容易,在sql语句中就能很好的获取到,这里我使用的是postgresql数据库,其他数据库应该也是使用了差不多的逻辑.

1.首先是获取当前时间:

select now(); //返回值：当前年月日、时分秒，且秒保留6位小数。

select current_date; //返回值：当前年月日、时分秒，且秒保留6位小数。（同now()）

select current_timestamp; //返回值：时分秒，秒最高精确到6位

select current_time; //返回值：年月日

2.获取当月的第一天:

select date_trunc('month',current_date); //date_trunc()函数用于设置时间基点,功能类似于java中的Calender工具类

3.获取上个月的最后一天:

select date_trunc('month',current_date) - interval'1 day' //基于当月第一天,倒退1天

4.获取本月最后一天:

select date_trunc('month',current_date) + interval'1 month - 1 day' //基于当月第一天,前进一个月,再倒退1天

(如当月第一天为 2020-08-01; +1 month 前进一个月为 2020-09-01; 再 - 1 day 倒退1天为 2020-08-31)

这里需要注意的是:

1.系统会自动根据运算结果的年份对应的月份,来计算天数的运算,也就是说不用关注2月份是28天还是29天,也不用关注有些月份是31天,有些月份是30天,系统就自动进行推演运算.

2.这里用到了date_trunc()函数,是用于建立时间基点:

如:date_trunc('month',current_date) 就是将当前时间对应的月份作为基点'month',这里自动默认选取月份的第一天作为基点.

3.interval'' 表达式,用于推演并计算日期时间,就是在原有基础上添加这个表达式中的内容,:

如:+ interval'1 month - 1 day' 就是在基点上添加内容为 ' 1个月 , -1天 ' ;这里面的 1 month没有带+符号为正数, 表示 前进一个月, -1 day 带-符号为负数, 则表示 倒退一天 ,因此在当月第一天的基础上前进一个月再倒退一天,从逻辑上推演就可以得知,结果为当月的最后一天.

4.根据以上的date_trunc()函数和interval'' 表达式,就可以获取到:

基于基点时间计算得到的任何一天的日期时间.

• 获取当前时间

SELECT now() as timestamp

• 获取本周时间

获取本周周一日期

SELECT to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval),'yyyy-mm-dd');

获取本周周日日期

SELECT to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2)+6 ||' days' as interval),'yyyy-mm-dd')

• 获取本月

select to_char((SELECT now() as timestamp),'mm')

• 获取上月

select to_char((select now() - interval '1 month'),'mm')

• 获取今年

select to_char((SELECT now() as timestamp),'yyyy')

• 获取去年

select to_char((select now() - interval '1 years'),'yyyy')

文章知识点与官方知识档案匹配，可进一步学习相关知识