PostgreSQL datediff 日期间隔(单位转换)兼容SQL用法

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 标签PostgreSQL , datediff背景使用datediff,对时间或日期相减,得到的间隔,转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周...

标签

PostgreSQL , datediff


背景

使用datediff,对时间或日期相减,得到的间隔,转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。

DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )  

select datediff(week,'2009-01-01','2009-12-31') as numweeks;  
  
numweeks  
----------  
52  
(1 row)  

季度

select datediff(qtr, '1998-07-01', current_date);  
  
date_diff  
-----------  
40  
(1 row)  

PostgreSQL中时间和日期可以相互加减,得到同样的结果使用extract。

PostgreSQL age, extract epoch

使用age函数对时间进行计算,得到interval。

使用extract epoch对interval 转换得到秒。

根据需求计算,转换为其他单位:日、月、季度、年、小时、秒等。

例子

postgres=# SELECT age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD'));  
   age     
---------  
 10 mons  
(1 row)  
  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')));  
 date_part   
-----------  
  25920000  
(1 row)  

重新计算即可

小时

。。。 。。。

例如

天  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')))/86400;  
 ?column?   
----------  
      300  
(1 row)  
  
月  
postgres=# SELECT EXTRACT(epoch FROM age(TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD'),TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD')))/86400/30;  
 ?column?   
----------  
       10  
(1 row)  

extract支持的时间单位

https://www.postgresql.org/docs/11/static/functions-datetime.html

src/backend/utils/adt/timestamp.c

参考

https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_DATEDIFF_function.html

https://www.postgresql.org/docs/11/static/functions-datetime.html

src/backend/utils/adt/timestamp.c

/* interval_part()  
 * Extract specified field from interval.  
 */  
Datum  
interval_part(PG_FUNCTION_ARGS)  
{  
        text       *units = PG_GETARG_TEXT_PP(0);  
        Interval   *interval = PG_GETARG_INTERVAL_P(1);  
        float8          result;  
        int                     type,  
                                val;  
        char       *lowunits;  
        fsec_t          fsec;  
        struct pg_tm tt,  
                           *tm = &tt;  
  
        lowunits = downcase_truncate_identifier(VARDATA_ANY(units),  
                                                                                        VARSIZE_ANY_EXHDR(units),  
                                                                                        false);  
  
        type = DecodeUnits(0, lowunits, &val);  
        if (type == UNKNOWN_FIELD)  
                type = DecodeSpecial(0, lowunits, &val);  
  
        if (type == UNITS)  
        {  
                if (interval2tm(*interval, tm, &fsec) == 0)  
                {  
                        switch (val)  
                        {  
                                case DTK_MICROSEC:  
                                        result = tm->tm_sec * 1000000.0 + fsec;  
                                        break;  
  
                                case DTK_MILLISEC:  
                                        result = tm->tm_sec * 1000.0 + fsec / 1000.0;  
                                        break;  
  
                                case DTK_SECOND:  
                                        result = tm->tm_sec + fsec / 1000000.0;  
                                        break;  
  
                                case DTK_MINUTE:  
                                        result = tm->tm_min;  
                                        break;  
  
                                case DTK_HOUR:  
                                        result = tm->tm_hour;  
                                        break;  
  
                                case DTK_DAY:  
                                        result = tm->tm_mday;  
                                        break;  
  
                                case DTK_MONTH:  
                                        result = tm->tm_mon;  
                                        break;  
  
                                case DTK_QUARTER:  
                                        result = (tm->tm_mon / 3) + 1;  
                                        break;  
  
                                case DTK_YEAR:  
                                        result = tm->tm_year;  
                                        break;  
  
                                case DTK_DECADE:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 10;  
                                        break;  
  
                                case DTK_CENTURY:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 100;  
                                        break;  
  
                                case DTK_MILLENNIUM:  
                                        /* caution: C division may have negative remainder */  
                                        result = tm->tm_year / 1000;  
                                        break;  
  
                                default:  
                                        ereport(ERROR,  
                                                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),  
                                                         errmsg("interval units \"%s\" not supported",  
                                                                        lowunits)));  
                                        result = 0;  
                        }  
  
                }  
                else  
                {  
                        elog(ERROR, "could not convert interval to tm");  
                        result = 0;  
                }  
        }  
        else if (type == RESERV && val == DTK_EPOCH)  
        {  
                result = interval->time / 1000000.0;  
                result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);  
                result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);  
                result += ((double) SECS_PER_DAY) * interval->day;  
        }  
        else  
        {  
                ereport(ERROR,  
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),  
                                 errmsg("interval units \"%s\" not recognized",  
                                                lowunits)));  
                result = 0;  
        }  
  
        PG_RETURN_FLOAT8(result);  
}  
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
18天前
|
SQL XML Java
mybatis复习03,动态SQL,if,choose,where,set,trim标签及foreach标签的用法
文章介绍了MyBatis中动态SQL的用法,包括if、choose、where、set和trim标签,以及foreach标签的详细使用。通过实际代码示例,展示了如何根据条件动态构建查询、更新和批量插入操作的SQL语句。
mybatis复习03,动态SQL,if,choose,where,set,trim标签及foreach标签的用法
|
4天前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
15 2
|
1月前
|
SQL 存储 安全
SQL Server用法
SQL Server用法
42 1
|
1月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
1月前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
37 1
|
2月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
35 3
|
2月前
|
SQL 安全 关系型数据库
PostgreSQL SQL注入漏洞(CVE-2018-10915)--处理
【8月更文挑战第8天】漏洞描述:PostgreSQL是一款自由的对象关系型数据库管理系统,支持多种SQL标准及特性。存在SQL注入漏洞,源于应用未有效验证外部输入的SQL语句,允许攻击者执行非法命令。受影响版本包括10.5及更早版本等。解决方法为升级PostgreSQL
199 2
|
2月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
74 0
|
2月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会:MySQL与SQL Server双轨对比教学,助你轻松搞定时间数据处理难题!
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统(如MySQL、SQL Server、Oracle)中常用的日期与时间函数,包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等,并提供了具体示例。通过对比这些函数在各系统中的使用方法,帮助开发者更高效地处理日期时间数据,满足多种应用场景需求。
102 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
176 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版