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

TIMESTAMPSUB

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

Subtract an interval of type java.lang.Number (number of days) or org.jooq.types.Interval (SQL interval type) from a timestamp (represented by java.sql.Timestamp).

SELECT DATE '2020-02-03 15:30:45' - INTERVAL 2 DAYS;
create.select(timestampSub(Timestamp.valueOf("2020-02-03 15:30:45"), 2)).fetch();

The result being

+---------------------+
| timestamp_sub       |
+---------------------+
| 2020-02-01 15:30:45 |
+---------------------+

Dialect support

This example using jOOQ:

timestampSub(Timestamp.valueOf("2020-02-03 15:30:45"), 2)

Translates to the following dialect specific expressions:

Access

dateadd('d', -2, #2020/02/03 15:30:45#)

ASE, Sybase

dateadd(DAY, -2, '2020-02-03 15:30:45.0')

Aurora MySQL, MariaDB, MySQL

date_add(TIMESTAMP '2020-02-03 15:30:45.0', INTERVAL -2 DAY)

Aurora Postgres, CockroachDB, Postgres, Redshift, YugabyteDB

(TIMESTAMP '2020-02-03 15:30:45.0' + -2 * INTERVAL '1 day')

BigQuery

timestamp_sub(DATETIME '2020-02-03 15:30:45.0', INTERVAL 2 DAY)

ClickHouse

(TIMESTAMP '2020-02-03 15:30:45' - 2)

DB2, HSQLDB

(TIMESTAMP '2020-02-03 15:30:45.0' - (2) day)

Derby

{fn timestampadd(SQL_TSI_DAY, -2, TIMESTAMP('2020-02-03 15:30:45.0')) }

DuckDB

date_add(TIMESTAMP '2020-02-03 15:30:45.0', INTERVAL 1 DAY * -2)

Exasol, H2, Oracle, Vertica

(TIMESTAMP '2020-02-03 15:30:45.0' - 2)

Firebird, Snowflake

dateadd(DAY, -2, TIMESTAMP '2020-02-03 15:30:45.0')

Hana

add_days(TIMESTAMP '2020-02-03 15:30:45.0', -2)

Informix

(DATETIME(2020-02-03 15:30:45.0) YEAR TO FRACTION - 2 UNITS DAY)

MemSQL

date_add({ts '2020-02-03 15:30:45.0'}, INTERVAL -2 DAY)

SQLDataWarehouse, SQLServer

dateadd(DAY, -2, CAST('2020-02-03 15:30:45.0' AS DATETIME2))

SQLite

strftime('%Y-%m-%d %H:%M:%f', '2020-02-03 15:30:45.0', (CAST(-2 AS varchar) || ' day'))

Teradata

TIMESTAMP '2020-02-03 15:30:45.0' - CAST(2 || ' 00:00:00' AS INTERVAL DAY TO SECOND)

Trino

date_add('day', -2, TIMESTAMP '2020-02-03 15:30:45.0')
Generated with jOOQ 3.20. Translate your own SQL on our website

References to this page

Feedback

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

The jOOQ Logo