SQL Ansi管理DateTime值 [英] Sql Ansi to manage DateTime values

问题描述

我正在开发一个多数据库系统.
我希望以秒为单位的两个日期之间的差异.

在 SQL Server 中，我得到了:
DATEDIFF(second,stardate,enddate)
在 MySql 中:
TIME_TO_SEC(TIMEDIFF(stardate,enddate))

我的问题:
Sql Ansi是否具有管理DateTime值的功能?
即:所有数据库都有通用的datetime函数吗?

I'm developing a multi-database system.
I want the difference between two dates in seconds.

In SQL Server I got:
DATEDIFF(second,stardate,enddate)
In MySql:
TIME_TO_SEC(TIMEDIFF(stardate,enddate))

My question:
Does Sql Ansi have functions to manage DateTime values?
i.e.: There are datetime functions generic for all databases?

推荐答案

根据SQL:1999，date1-date0应该为您提供INTERVAL类型的值，该结构应该可以从中提取YEAR，MONTH，DAY等

According to SQL:1999, date1-date0 should give you a value of type INTERVAL, a struct from which you should be able to extract YEAR, MONTH, DAY, etc.

我从未使用过它，并且我不认为它得到了广泛支持(尽管我可能不是最新的).如果您要在数据库层中执行时间算术，并且想与DBMS兼容，通常的解决方案就是简单地使用整数时间戳(具有任何分辨率，但Unix时间是通用的)和普通的整数算术，这是完全可靠的. -平台.

I've never used it and I don't think it's widely supported (though I may not be up-to-date). If you're doing time arithmetic in the database layer and you want to be cross-DBMS compatible the usual solution is simply to use integer timestamps (of whatever resolution, but Unix time is common) and plain old integer arithmetic which is completely reliable cross-platform.

这篇关于SQL Ansi管理DateTime值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！