sql 提取日期中的分钟_如何在SQL中提取日期部分-CSDN博客

本文探讨了如何在SQL中正确地提取日期时间的分钟部分，引用了Modern SQL的相关推文，并通过jOOQ在18个不同RDBMS上的测试展示了标准的实现情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql 提取日期中的分钟

Modern SQL Twitter帐户（由Markus Winand撰写）发布了有关如何在SQL中提取日期部分的提示：

获取日期/时间的一部分的正确方法是：EXTRACT（YEAR FROM CURRENT_DATE）= 2015 http://t.co/UNLyUoQdVb转推以传播这个词！

— Modern SQL（@ModernSQL） 2015年2月24日

是真的吗是的，在SQL标准和各种符合标准的数据库中。但是，让我们检查一下，在当前支持的所有18个RDBMS上运行以下程序时，jOOQ会做什么：

import static org.jooq.impl.DSL.currentDate;
import static org.jooq.impl.DSL.extract;
import static org.jooq.impl.DSL.using;

import java.util.stream.Stream;

import org.jooq.DatePart;
import org.jooq.SQLDialect;

public class Extract {
    public static void main(String[] args) {
        // Get all distinct SQLDialect families
        Stream
        .of(SQLDialect.values())
        .map(SQLDialect::family)
        .distinct()
        .forEach(family -> {
            System.out.println();
            System.out.println(family);

            // Get all supported date parts
            Stream
            .of(DatePart.values())

            // For each family / part, get the
            // EXTRACT() function
            .map(part -> extract(currentDate(), part))
            .forEach(expr -> {
                System.out.println(
                    using(family).render(expr)
                );
            });
        });
    }
}

输出为：

开源数据库

DEFAULT
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

CUBRID
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

DERBY
year(current_date)
month(current_date)
day(current_date)
hour(current_date)
minute(current_date)
second(current_date)

FIREBIRD
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

H2
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

HSQLDB
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

MARIADB
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

MYSQL
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

POSTGRES
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

SQLITE
strftime('%Y', current_date)
strftime('%m', current_date)
strftime('%d', current_date)
strftime('%H', current_date)
strftime('%M', current_date)
strftime('%S', current_date)

商业数据库

ACCESS
datepart('yyyy', date())
datepart('m', date())
datepart('d', date())
datepart('h', date())
datepart('n', date())
datepart('s', date())

ASE
datepart(yy, current_date())
datepart(mm, current_date())
datepart(dd, current_date())
datepart(hh, current_date())
datepart(mi, current_date())
datepart(ss, current_date())

DB2
year(current_date)
month(current_date)
day(current_date)
hour(current_date)
minute(current_date)
second(current_date)

HANA
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

INFORMIX
year(current year to day)
month(current year to day)
day(current year to day)
current year to day::datetime hour to hour::char(2)::int
current year to day::datetime minute to minute::char(2)::int
current year to day::datetime second to second::char(2)::int

INGRES
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

ORACLE (in jOOQ 3.5)
to_char(trunc(sysdate), 'YYYY')
to_char(trunc(sysdate), 'MM')
to_char(trunc(sysdate), 'DD')
to_char(trunc(sysdate), 'HH24')
to_char(trunc(sysdate), 'MI')
to_char(trunc(sysdate), 'SS')

ORACLE (in jOOQ 3.6)
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from cast(current_date as timestamp))
extract(minute from cast(current_date as timestamp))
extract(second from cast(current_date as timestamp))

SQLSERVER
datepart(yy, convert(date, current_timestamp))
datepart(mm, convert(date, current_timestamp))
datepart(dd, convert(date, current_timestamp))
datepart(hh, convert(date, current_timestamp))
datepart(mi, convert(date, current_timestamp))
datepart(ss, convert(date, current_timestamp))

SYBASE
datepart(yy, current date)
datepart(mm, current date)
datepart(dd, current date)
datepart(hh, current date)
datepart(mi, current date)
datepart(ss, current date)

是。标准……如果它得到彻底实施……

翻译自: https://www.javacodegeeks.com/2015/03/how-to-extract-a-date-part-in-sql.html

sql 提取日期中的分钟