Skip to content

Latest commit

 

History

History
151 lines (114 loc) · 5.68 KB

date_sub.md

File metadata and controls

151 lines (114 loc) · 5.68 KB
description
Returns the difference of two expressions of time as another expression of time.

DATE_SUB

Syntax

DATE_SUB(date_expression STRING, days INTEGER) → DATE

  • date_expression: A string-formatted date in the format ‘YYYY-MM-DD’.
  • days: The number of days to be subtracted from the specified date.

Examples

{% code title="Subtracts two days from the specified date." %}

SELECT DATE_SUB('2022-01-01', 2)
-- 2021-12-30

{% endcode %}

{% code title="Subtracts negative two days from the specified date." %}

SELECT DATE_SUB('2022-01-01', -2)
-- 2022-01-03

{% endcode %}

DATE_SUB(date_expression DATE, days INTEGER) → DATE

  • date_expression: The date, in the format ‘YYY-MM-DD’, to subtract days from. The value can be either a database column in DATE format, or literal value explicitly converted to DATE.
  • days: A 32-bit integer of the number of days to be subtracted from the specified date.

Examples

{% code title="Subtracts 30 days from the specified date." %}

SELECT DATE_SUB(DATE '2022-01-01', 30)
-- 2021-12-02

{% endcode %}

Subtracts negative 30 days from the specified date.

{% code title="Subtracts negative 30 days from the specified date." %}

SELECT DATE_SUB(DATE '2022-01-01', -30)
-- 2022-01-31

{% endcode %}

DATE_SUB(date_expression STRING, time_interval INTERVAL) → TIMESTAMP

  • date_expression: A string-formatted date in the format ‘YYYY-MM-DD’.
  • time_interval: A CAST of a number to one of these intervals: DAY, MONTH, YEAR.

Examples

{% code title="Subtracts two days from the specified date." %}

SELECT DATE_SUB('2022-01-01', CAST(2 AS INTERVAL DAY))
-- 2021-12-30 00:00:00

{% endcode %}

{% code title="Subtracts negative two days from the specified date." %}

SELECT DATE_SUB('2022-01-01', CAST(-2 AS INTERVAL DAY))
-- 2022-01-03 00:00:00

{% endcode %}

DATE_SUB(date_expression DATE, time_interval INTERVAL) → TIMESTAMP

  • date_expression: The date, in the format ‘YYY-MM-DD’, to subtract the time interval from. The value can be either a database column in DATE format, or literal value explicitly converted to DATE.
  • time_interval: A CAST of a number to one of these intervals: DAY, MONTH, YEAR.

Examples

{% code title="DATE_SUB example" %}

SELECT DATE_SUB(DATE '2022-01-01', CAST(30 AS INTERVAL DAY))
-- 2021-12-02 00:00:00

{% endcode %}

DATE_SUB(timestamp_expression STRING, time_interval INTERVAL) → TIMESTAMP

  • timestamp_expression: A string-formatted timestamp in the format ‘YYYY-MM-DD HH24:MI:SS’.
  • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.

Examples

{% code title="Subtracts 30 days from the specified timestamp. Note that the time information is lost." %}

SELECT DATE_SUB('2022-01-01 12:00:00', CAST(30 AS INTERVAL DAY))
-- 2021-12-02 00:00:00

{% endcode %}

{% code title="Subtracts negative 30 days from the specified timestamp. Note that the time information is lost." %}

SELECT DATE_SUB('2022-01-01 12:00:00', CAST(-30 AS INTERVAL DAY))
-- 2022-01-31 00:00:00

{% endcode %}

DATE_SUB(timestamp_expression TIMESTAMP, time_interval INTERVAL) → TIMESTAMP

  • timestamp_expression: The timestamp, in the format ‘YYYY-MM-DD HH:MM:SS’, to subtract days from. The value can be either a database column in TIMESTAMP format, or literal value explicitly converted to TIMESTAMP.
  • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.

Examples

{% code title="Subtracts 30 days from the specified timestamp." %}

SELECT DATE_SUB(TIMESTAMP '2022-01-01 12:00:00', CAST(30 AS INTERVAL DAY))
-- 2021-12-02 12:00:00

{% endcode %}

{% code title="Subtracts negative 30 days from the specified timestamp." %}

SELECT DATE_SUB(TIMESTAMP '2022-01-01 12:00:00', CAST(-30 AS INTERVAL DAY))
-- 2022-01-31 12:00:00

{% endcode %}

DATE_SUB(time_expression TIME, time_interval INTERVAL) → TIME

  • time_expression: The time, in the format ‘HH:MM:SS’, to subtract the time interval from. The value can be either a database column in TIMESTAMP format, or literal value explicitly converted to TIMESTAMP.
  • time_interval: A CAST of a number to one of these intervals: SECOND, MINUTE, HOUR.

Examples

{% code title="Subtracts 30 minutes from the specified time." %}

SELECT DATE_SUB(TIME '00:00:00', CAST(30 AS INTERVAL MINUTE))
-- 84600

{% endcode %}

{% code title="Subtracts negative 30 minutes from the specified time." %}

SELECT DATE_SUB(TIME '00:00:00', CAST(-30 AS INTERVAL MINUTE))
-- 00:30:00

{% endcode %}