【SQL开发实战技巧】系列(十八):数据仓库中时间类型操作(进阶)INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算

简介: 日期操作函数(INTERVAL、EXTRACT)的使用以及如何确定一年是否为闰年及周的计算两个小案例【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本章主要介绍的是关于时间类型的一些常规操作。

前言

本篇文章讲解的主要内容是:日期操作函数(INTERVAL、EXTRACT)的使用以及如何确定一年是否为闰年及周的计算两个小案例
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、SYSDATE能得到的信息

经常看到有人因为不熟悉日期操作,获取相应信息的时候,要写很复杂的语句。下面举一个简单的例子。

SQL> select hiredate,
  2         to_date(to_char(hiredate, 'yyyy-mm') || '-1', 'yyyy-mm-dd') as yuechu
  3    from emp
  4   where rownum <= 1;

HIREDATE    YUECHU
----------- -----------
1980-12-17  1980-12-1

SQL> 

其实要获取这个数据,只需要一个简单的函数就可以做到,而根本不需要多次转换:

SQL> SELECT hiredate AS 雇佣日期, trunc(hiredate, 'mm') AS 月初
  2    from emp
  3   where rownum <= 1;

雇佣日期    月初
----------- -----------
1980-12-17  1980-12-1

SQL> 

下面列举几个常用的取值方式,希望对大家有用。

SQL> SELECT hiredate,
  2  to_number(to_char(hiredate,'hh24'))时,
  3  to_number(to_char(hiredate,'mi'))分,
  4  to_number(to_char(hiredate,'ss'))秒,
  5  to_number(to_char(hiredate,'dd'))日,
  6  to_number(to_char(hiredate,'mm'))月,
  7  to_number(to_char(hiredate,'yyyy'))年,
  8  to_number(to_char(hiredate,'ddd'))年内第几天,
  9  trunc(hiredate,'dd')一天之始,
 10  trunc(hiredate,'day')周初,
 11  trunc(hiredate,'dy')周初,
 12  trunc(hiredate,'mm')月初,
 13  last_day(hiredate)月未,
 14  add_months(trunc(hiredate,'mm'),1)下月初,
 15  trunc(hiredate,'yy')年初,
 16  to_char(hiredate,'day')周几,
 17  to_char(hiredate,'dy')周几,
 18  to_char(hiredate,'month')月份,
 19  to_char(hiredate,'mm')月份
 20  FROM(SELECT hiredate+30/24/60/60+20/24/60+5/24 AS hiredate FROM emp WHERE ROWNUM<=1);

HIREDATE             时          分          秒          日          月          年      年内第几天 一天之始    周初        周初        月初        月未        下月初      年初        周几                                                                        周几                                                                        月份                                                                        月份
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1980-12-17           5         20         30         17         12       1980        352 1980-12-17  1980-12-14  1980-12-14  1980-12-1   1980-12-31  1981-1-1    1980-1-1    星期三                                                                      星期三                                                                      12月                                                                        12

SQL> 

需要注意的是上面last_day的用法,该函数返回的时分秒与参数中的一样,如果用该函数作为区间条件,会发生下面的情况。

SQL> 
SQL> with t as (
  2  select
  3  to_date('1980-12-31 15:20:30','yyyy-mm-dd hh24:mi:ss') as d1,
  4  to_date('1980-12-31 05:20:30','yyyy-mm-dd hh24:mi:ss') as d2
  5  from dual
  6  )
  7  select d1,d2 from t where d1 between trunc(d2,'mm') and last_day(d2);

D1          D2
----------- -----------

SQL> 

若要取一个月的数据,应该用下面的方式。

with t as (
select 
to_date('1980-12-31 15:20:30','yyyy-mm-dd hh24:mi:ss') as d1,
to_date('1980-12-31 05:20:30','yyyy-mm-dd hh24:mi:ss') as d2
from dual
)
select d1,d2 from t where d1>=trunc(d2,'mm') and d1<add_months(trunc(d2,'mm'),1);
        D1                D2
----------------------------
1980-12-31 15:20:30    1980-12-31 5:20:30
    

二、INTERVAL

INTERVAL类型中保存的是时间间隔信息,可以通过对应的INTERVAL函数得到INTERVAL类型的数据。

SQL> select INTERVAL '2' year as "year",
  2  INTERVAL '50' month as "month",
  3  INTERVAL '99' day as "day",/*最大只能到99*/
  4  INTERVAL'80' hour as  "hour",
  5  INTERVAL'90' minute as "minute",
  6  INTERVAL'3.15' second as "second",
  7  INTERVAL'2 12:30:59'DAY to second as "DAY to second",
  8  INTERVAL'13-3' year  to month as "Year  to  month" from dual;

year                                                                            month                                                                           day                                                                             hour                                                                            minute                                                                          second                                                                          DAY to second                                                                   Year  to  month
------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------
+02-00                                                                          +04-02                                                                          +99 00:00:00                                                                    +03 08:00:00                                                                    +00 01:30:00                                                                    +00 00:00:03.150000                                                             +02 12:30:59.000000                                                             +13-03

SQL> 

当增加一个较复杂的时间段时,如上面的"02天12小时30分59秒“,通过INTERVAL实现显然更直观。

三、EXTRACT

TO_CHAR一样,EXTRACT可以提取时间字段中的年、月、日、时、分、秒。不同的是,EXTRACT的返回值为NUMBER类型。

create table testa as 
select 
extract(year from systimestamp) as "year",
extract(month from systimestamp) as "month",
extract(day from systimestamp) as "day",
extract(hour from systimestamp) as "hour",
extract(minute from systimestamp) as "minute",
extract(second from systimestamp) as "second"
from dual;
SQL> desc testa;
Name   Type   Nullable Default Comments 
------ ------ -------- ------- -------- 
year   NUMBER Y                         
month  NUMBER Y                         
day    NUMBER Y                         
hour   NUMBER Y                         
minute NUMBER Y                         
second NUMBER Y                         

SQL> 

EXTRACT不能取DATE中的时、分、秒,示例如下:

SQL> select
  2  extract(hour from sysdate) as "hour",
  3  extract(minute from sysdate) as "minute",
  4  extract(second from sysdate) as "second"
  5  from dual;
select
extract(hour from sysdate) as "hour",
extract(minute from sysdate) as "minute",
extract(second from sysdate) as "second"
from dual

ORA-30076: 对析出来源无效的析出字段

SQL>     

TO_CHAR可以,示例如下:

SQL> select
  2  to_char(sysdate,'dd') as dd,
  3  to_char(sysdate,'hh24') as hh,
  4  to_char(sysdate,'mi') as mi,
  5  to_char(sysdate,'ss') as ss
  6  from dual;

DD                                                                          HH                                                                          MI                                                                          SS
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
15                                                                          14                                                                          43                                                                          30

SQL> 

EXTRACT可以取INTERVAL中的信息,示例如下:

SQL> select extract(hour from it) as "hour"
  2  from (
  3  select INTERVAL'2 12:30:59'DAY to second   as it from dual
  4  );

      hour
----------
        12

SQL>     

TO_CHAR不行,示例如下:

SQL> select to_Char(it,'hh24') as "hour"
  2  from (
  3  select INTERVAL'2 12:30:59'DAY to second   as it from dual
  4  );

hour
--------------------
+02 12:30:59.000000

SQL> 

四、确定一年是否为闰年

若要判断一年是否为闰年,只需要看二月的月末是哪一天就可以。

SQL> select trunc(sysdate, 'y') as 年初,
  2         add_months(trunc(sysdate, 'y'), 1) as 二月初,
  3         last_day(add_months(trunc(sysdate, 'y'), 1)) as 二月底,
  4         to_char(last_day(add_months(trunc(sysdate, 'y'), 1)), 'dd') as 日
  5    from dual;

年初        二月初      二月底      日
----------- ----------- ----------- ---------------------------------------------------------------------------
2023-1-1    2023-2-1    2023-2-28   28

SQL> 

五、周的计算

SQL> with t as (
  2  select trunc(sysdate,'YY')+(level-1) as 日期
  3  from dual
  4  connect by level<=8
  5  )
  6  select 日期,
  7  /*返回值1代表周日,2代表周一*/
  8  to_char(日期,'d') as d,
  9  to_char(日期,'day') as day,
 10  /*参数2中1代表周日,2代表周一*/
 11  next_day(日期,1) as 下个周日,
 12  /*ww的算法为每年1月1日为第一周开始,date+6为每一周结束*/
 13  to_char(日期,'ww') as ww,
 14  /*iw的算法为星期一至星期日算一周,且每年的第一个星期一为第一周*/
 15  to_char(日期,'iw') as iw
 16  from t;

日期        D                                                                           DAY                                                                         下个周日    WW                                                                          IW
----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2023-1-1    1                                                                           星期日                                                                      2023-1-8    01                                                                          52
2023-1-2    2                                                                           星期一                                                                      2023-1-8    01                                                                          01
2023-1-3    3                                                                           星期二                                                                      2023-1-8    01                                                                          01
2023-1-4    4                                                                           星期三                                                                      2023-1-8    01                                                                          01
2023-1-5    5                                                                           星期四                                                                      2023-1-8    01                                                                          01
2023-1-6    6                                                                           星期五                                                                      2023-1-8    01                                                                          01
2023-1-7    7                                                                           星期六                                                                      2023-1-8    01                                                                          01
2023-1-8    1                                                                           星期日                                                                      2023-1-15   02                                                                          01

8 rows selected


SQL> 

注意以下两点:

  • 参数"day"与字符集有关,所以提倡改用"d"。
  • WW与IW都是取“第几周”,只是两个参数的初始值不一样。

总结

本章主要介绍的是关于时间类型的一些常规操作。

相关文章
|
4月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
5月前
|
SQL Cloud Native 调度
云原生数据仓库使用问题之如何修改历史数据清理的SQL
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
1月前
|
SQL 存储 算法
不用 SQL 的数据仓库
esProc 是一种非 SQL 型数据仓库,采用 SPL 作为查询语言,旨在解决 SQL 在复杂计算、开放性和性能等方面的局限。SPL 支持过程计算、有序运算及丰富的数据类型,能够直接处理多样数据源,无需数据先入库。此外,esProc 通过文件存储和高性能算法优化,实现了计算与存储的解绑,降低了运维成本,提升了计算性能。
|
3月前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
在Web开发中,安全至关重要,尤其要警惕SQL注入和XSS攻击。SQL注入通过在数据库查询中插入恶意代码来窃取或篡改数据,而XSS攻击则通过注入恶意脚本来窃取用户敏感信息。本文将带你深入了解这两种威胁,并提供Python实战技巧,包括使用参数化查询和ORM框架防御SQL注入,以及利用模板引擎自动转义和内容安全策略(CSP)防范XSS攻击。通过掌握这些方法,你将能够更加自信地应对Web安全挑战,确保应用程序的安全性。
101 3
|
4月前
|
SQL NoSQL 数据库
开发效率与灵活性:SQL vs NoSQL
【8月更文第24天】随着大数据和实时应用的兴起,数据库技术也在不断发展以适应新的需求。传统的SQL(结构化查询语言)数据库因其成熟的数据管理机制而被广泛使用,而NoSQL(Not Only SQL)数据库则以其灵活性和扩展性赢得了众多开发者的青睐。本文将从开发者的视角出发,探讨这两种数据库类型的优缺点,并通过具体的代码示例来说明它们在实际开发中的应用。
127 1
|
3月前
|
SQL 分布式计算 大数据
大数据开发SQL代码编码原则和规范
这段SQL编码原则强调代码的功能完整性、清晰度、执行效率及可读性,通过统一关键词大小写、缩进量以及禁止使用模糊操作如select *等手段提升代码质量。此外,SQL编码规范还详细规定了代码头部信息、字段与子句排列、运算符前后间隔、CASE语句编写、查询嵌套、表别名定义以及SQL注释的具体要求,确保代码的一致性和维护性。
111 0
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
488 0
|
5月前
|
Cloud Native 关系型数据库 OLAP
云原生数据仓库操作报错合集之遇到“table does not exist”错误,该怎么办
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
5月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库操作报错合集之遇到报错“DDL forbidden because backupTask is doing snapshot”如何处理
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
5月前
|
JSON Cloud Native 关系型数据库
云原生数据仓库操作报错合集之遇到报错 "ERROR: out of shared memory" ,该怎么办
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。