Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Datetime functions

Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

Datetime functions are useful to calculate date time arithmetic and formatting.

Many functions in this section come with two flavours supporting both the JDBC datetime data types, and the JSR 310 types. These include:

SQL DATE modelled by java.time.LocalDate and JDBC's java.sql.Date
SQL TIME modelled by java.time.LocalTime and JDBC's java.sql.Time
SQL TIMESTAMP modelled by java.time.LocalDateTime and JDBC's java.sql.Timestamp

Some temporal SQL data types could not be represented canonically with historic JDBC types, but only with JSR 310 types. These include:

SQL TIME WITH TIME ZONE modelled by java.time.OffsetTime
SQL TIMESTAMP WITH TIME ZONE modelled by any of java.time.Instant (e.g. PostgreSQL), java.time.OffsetDateTime (JDBC and standard SQL), as well as java.time.ZonedDateTime (e.g. Oracle)

3.11.18.1.: CENTURY
3.11.18.2.: CURRENT_DATE
3.11.18.3.: CURRENT_LOCALDATE
3.11.18.4.: CURRENT_LOCALDATETIME
3.11.18.5.: CURRENT_LOCALTIME
3.11.18.6.: CURRENT_OFFSETDATETIME
3.11.18.7.: CURRENT_OFFSETTIME
3.11.18.8.: CURRENT_TIME
3.11.18.9.: CURRENT_TIMESTAMP
3.11.18.10.: DATE
3.11.18.11.: DATEADD
3.11.18.12.: DATEDIFF
3.11.18.13.: DATESUB
3.11.18.14.: DAY
3.11.18.15.: DAY_OF_YEAR
3.11.18.16.: DECADE
3.11.18.17.: EPOCH
3.11.18.18.: EXTRACT
3.11.18.19.: HOUR
3.11.18.20.: ISO_DAY_OF_WEEK
3.11.18.21.: LOCALDATE
3.11.18.22.: LOCALDATEADD
3.11.18.23.: LOCALDATESUB
3.11.18.24.: LOCALDATETIME
3.11.18.25.: LOCALDATETIMEADD
3.11.18.26.: LOCALDATETIMESUB
3.11.18.27.: LOCALTIME
3.11.18.28.: MILLENNIUM
3.11.18.29.: MINUTE
3.11.18.30.: MONTH
3.11.18.31.: QUARTER
3.11.18.32.: SECOND
3.11.18.33.: TIME
3.11.18.34.: TIMESTAMP
3.11.18.35.: TIMESTAMPADD
3.11.18.36.: TIMESTAMPSUB
3.11.18.37.: TO_DATE
3.11.18.38.: TO_LOCALDATE
3.11.18.39.: TO_LOCALDATETIME
3.11.18.40.: TO_TIMESTAMP
3.11.18.41.: TRUNC
3.11.18.42.: YEAR

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

Tell us a little bit about yourself and join our newsletter (examples)

Datetime functions

Table of contents

Feedback

Community

Support

Legal

Documentation