最近在使用OB日期相关函数，整理了下常用的一些日期函数。例如，如何获取当月第一天，当月最后一天等等。发现OB日期函数（mysql模式）还是相比Oracle有些需要改进的地方。例如，找不到trunc相关的截断日期的函数。
下面是我整理的一些日期函数，不对的地方欢迎各位指出。
1: 当前日期
select curdate()
obclient [oceanbase]> select curdate();
+------------+
| curdate() |
+------------+
| 2023-01-10 |
+------------+
1 row in set (0.001 sec)
2： 当月第一天
select concat(date_format(curdate(),’%Y%m’),‘01’);
obclient [oceanbase]> select concat(date_format(curdate(),'%Y%m'),'01');
+--------------------------------------------+
| concat(date_format(curdate(),'%Y%m'),'01') |
+--------------------------------------------+
| 20230101 |
+--------------------------------------------+
3: 本月月末
select date_sub(concat(date_format(date_add(curdate(),interval 1 month),’%Y%m’),‘01’),interval 1 day)
obclient [oceanbase]> select date_sub(concat(date_format(date_add(curdate(),interval 1 month),'%Y%m'),'01'),interval 1 day);
+------------------------------------------------------------------------------------------------+
| date_sub(concat(date_format(date_add(curdate(),interval 1 month),'%Y%m'),'01'),interval 1 day) |
+------------------------------------------------------------------------------------------------+
| 2023-01-31 |
+------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
4： 下月第一天
select concat(date_format(date_add(curdate(),interval 1 month),’%Y%m’),‘01’);
obclient [oceanbase]> select concat(date_format(date_add(curdate(),interval 1 month),'%Y%m'),'01');
+-----------------------------------------------------------------------+
| concat(date_format(date_add(curdate(),interval 1 month),'%Y%m'),'01') |
+-----------------------------------------------------------------------+
| 20230201 |
+-----------------------------------------------------------------------+
1 row in set (0.002 sec)
5: 季度第一天
select case when extract(quarter from current_date())=1 then concat(extract(YEAR from current_date()),‘0101’)
when extract(quarter from current_date())=2 then concat(extract(YEAR from current_date()),‘0401’)
when extract(quarter from current_date())=3 then concat(extract(YEAR from current_date()),‘0701’)
else concat(extract(YEAR from current_date()),‘1001’) end
obclient [oceanbase]> select case when extract(quarter from current_date())=1 then concat(extract(YEAR from current_date()),'0101') when extract(quarter from current_date())=2 then concat(extract(YEAR from current_date()),'0401') when extract(quarter from current_date())=3 then concat(extract(YEAR from current_date()),'0701') else concat(extract(YEAR from current_date()),'1001') end quota_first;
+-------------+
| quota_first |
+-------------+
| 20230101 |
+-------------+
1 row in set (0.002 sec)
6： 本年第一天
select concat(date_format(curdate(),’%Y’),‘0101’);
obclient [oceanbase]> select concat(date_format(curdate(),'%Y'),'0101');
+--------------------------------------------+
| concat(date_format(curdate(),'%Y'),'0101') |
+--------------------------------------------+
| 20230101 |
+--------------------------------------------+
1 row in set (0.002 sec)
7： 本年最后一天
select concat(date_format(curdate(),’%Y’),‘1231’);
obclient [oceanbase]> select concat(date_format(curdate(),'%Y'),'1231');
+--------------------------------------------+
| concat(date_format(curdate(),'%Y'),'1231') |
+--------------------------------------------+
| 20231231 |
+--------------------------------------------+
1 row in set (0.001 sec)
8: 来年第一天
select concat(date_format(curdate(),’%Y’)+1,‘0101’);
obclient [oceanbase]> select concat(date_format(curdate(),'%Y')+1,'0101');
+----------------------------------------------+
| concat(date_format(curdate(),'%Y')+1,'0101') |
+----------------------------------------------+
| 20240101 |
+----------------------------------------------+
1 row in set (0.002 sec)
9： 年度第几天
select dayofyear(curdate());
obclient [oceanbase]> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
| 10 |
+----------------------+
1 row in set (0.001 sec)
10: 本周第一天(以周一为第一天)
select date_sub(curdate(),interval weekday(curdate()) day);
obclient [oceanbase]> select date_sub(curdate(),interval weekday(curdate()) day);
+-----------------------------------------------------+
| date_sub(curdate(),interval weekday(curdate()) day) |
+-----------------------------------------------------+
| 2023-01-09 |
+-----------------------------------------------------+
1 row in set (0.001 sec)
11: 本周第一天(以周日为第一天)
select date_sub(curdate(),interval dayofweek(curdate())-1 day);
obclient [oceanbase]> select date_sub(curdate(),interval dayofweek(curdate())-1 day);
+---------------------------------------------------------+
| date_sub(curdate(),interval dayofweek(curdate())-1 day) |
+---------------------------------------------------------+
| 2023-01-08 |
+---------------------------------------------------------+
1 row in set (0.002 sec)
12：本周最后一天(以周日为最后一天)
select date_add(date_sub(curdate(),interval weekday(curdate()) day),interval 6 day);
obclient [oceanbase]> select date_add(date_sub(curdate(),interval weekday(curdate()) day),interval 6 day);
+------------------------------------------------------------------------------+
| date_add(date_sub(curdate(),interval weekday(curdate()) day),interval 6 day) |
+------------------------------------------------------------------------------+
| 2023-01-15 |
+------------------------------------------------------------------------------+
1 row in set (0.001 sec)
13：本周最后一天(以周六为最后一天)
select date_add(date_sub(curdate(),interval dayofweek(curdate())-1 day),interval 6 day);
obclient [oceanbase]> select date_add(date_sub(curdate(),interval dayofweek(curdate())-1 day),interval 6 day);
+----------------------------------------------------------------------------------+
| date_add(date_sub(curdate(),interval dayofweek(curdate())-1 day),interval 6 day) |
+----------------------------------------------------------------------------------+
| 2023-01-14 |
+----------------------------------------------------------------------------------+
1 row in set (0.001 sec)
14：当前日期加1天1小时2分3秒
select date_add(curdate(),interval ‘01 01:02:03’ day_second) ;
obclient [oceanbase]> select date_add(curdate(),interval '01 01:02:03' day_second) ;
+-------------------------------------------------------+
| date_add(curdate(),interval '01 01:02:03' day_second) |
+-------------------------------------------------------+
| 2023-01-11 01:02:03 |
+-------------------------------------------------------+
1 row in set (0.001 sec)
15：日期类型减数字可能会出问题
select curdate(),dayofmonth(curdate()),curdate() - dayofmonth(curdate());
±-----------±----------------------±----------------------------------+
| curdate() | dayofmonth(curdate()) | curdate() - dayofmonth(curdate()) |
±-----------±----------------------±----------------------------------+
| 2023-01-10 | 10 | 20230100 |
±-----------±----------------------±----------------------------------+
obclient [oceanbase]> select date_sub(curdate() ,interval dayofmonth(curdate()) day);
+---------------------------------------------------------+
| date_sub(curdate() ,interval dayofmonth(curdate()) day) |
+---------------------------------------------------------+
| 2022-12-31 |
+---------------------------------------------------------+
--oracle类型dba当心这个坑。因为在oracle里这个是正常的。