Date Functions

Supported Date Functions

CURRENT_DATE

CURRENT_DATE()

Returns the current date

Example

SELECT
    CURRENT_DATE() AS exprVal
FROM `customers`

DATE_FROM_ISO_PARTS

DATE_FROM_ISO_PARTS(isoWeekYear, isoWeek, isoDayOfWeek, hour, second, minute, millisecond, timezone)

Constructs and returns a Date object given the date’s constituent ISO properties.

Example

SELECT
    DATE_FROM_ISO_PARTS(2017, 6, 3, null, null, null, null, null) AS exprVal
FROM `customers`

DATE_FROM_PARTS

DATE_FROM_PARTS(year, month, day, hour, second, minute, millisecond, timezone)

Constructs and returns a Date object given the date’s constituent properties.

Example

SELECT
    DATE_FROM_PARTS(2021, 11, 15, null, null, null, null, null) AS exprVal
FROM `customers`

DATE_FROM_STRING

DATE_FROM_STRING(expr, format, timezone, onError, onNull)

Converts a date/time string to a date object.

Example

SELECT
    DATE_FROM_STRING('2021-11-15T14:43:29.000Z', null, null) AS exprVal
FROM `customers`

DATE_TO_PARTS

DATE_TO_PARTS(expr, timezone, iso8601)

Returns a document that contains the constituent parts of a given Date value.

Example

SELECT
    DATE_TO_PARTS(DATE_FROM_STRING('2021-11-15T14:43:29.000Z'), null, true) AS exprVal
FROM `customers`

DATE_TO_STRING

DATE_TO_STRING(expr, format, timezone, onNull)

Converts a date object to a string according to a user-specified format.

Example

SELECT
    DATE_TO_STRING(DATE_FROM_STRING('2021-11-15T14:43:29.000Z'), null, null) AS exprVal
FROM `customers`

DAY_OF_MONTH

DAY_OF_MONTH(expr)

Returns the day of the month for a date as a number between 1 and 31.

Example

SELECT
    DAY_OF_MONTH(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

DAY_OF_WEEK

DAY_OF_WEEK(expr)

Returns the day of the week for a date as a number between 1 (Sunday) and 7 (Saturday).

Example

SELECT
    DAY_OF_WEEK(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

DAY_OF_YEAR

DAY_OF_YEAR(expr)

Returns the day of the year for a date as a number between 1 and 366.

Example

SELECT
    DAY_OF_YEAR(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

HOUR

HOUR(expr)

Returns the hour portion of a date as a number between 0 and 23.

Example

SELECT
    HOUR(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

ISO_DAY_OF_WEEK

ISO_DAY_OF_WEEK(expr)

Returns the weekday number in ISO 8601 format, ranging from 1 (for Monday) to 7 (for Sunday).

Example

SELECT
    ISO_DAY_OF_WEEK(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

ISO_WEEK

ISO_WEEK(expr)

Returns the week number in ISO 8601 format, ranging from 1 to 53. Week numbers start at 1 with the week (Monday through Sunday) that contains the year’s first Thursday.

Example

SELECT
    ISO_WEEK(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

ISO_WEEK_YEAR

ISO_WEEK_YEAR(expr)

Returns the year number in ISO 8601 format. The year starts with the Monday of week 1 and ends with the Sunday of the last week.

Example

SELECT
    ISO_WEEK_YEAR(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

MILLISECOND

MILLISECOND(expr)

Returns the millisecond portion of a date as an integer between 0 and 999.

Example

SELECT
    MILLISECOND(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

MINUTE

MINUTE(expr)

Returns the minute portion of a date as a number between 0 and 59.

Example

SELECT
    MINUTE(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

MONTH

MONTH(expr)

Returns the month of a date as a number between 1 and 12.

Example

SELECT
    MONTH(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

SECOND

SECOND(expr)

Returns the second portion of a date as a number between 0 and 59, but can be 60 to account for leap seconds.

Example

SELECT
    SECOND(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

WEEK

WEEK(expr)

Returns the week of the year for a date as a number between 0 and 53.

Example

SELECT
    WEEK(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

YEAR

YEAR(expr)

Returns the year portion of a date.

Example

SELECT
    YEAR(DATE_FROM_STRING('2021-11-15')) AS exprVal
FROM `customers`

EXTRACT

EXTRACT(period from expr)

Extracts a portion of the date as per Postgres standard. Supported time periods: year, month, day, hour, minute, second, milliseconds, week, dow

Example

SELECT
    EXTRACT(year from orderDate) AS year
    ,EXTRACT(month from orderDate) AS month
    ,EXTRACT(day from TO_DATE('2021-10-23')) AS day
FROM
  orders"

DATE_ADD

DATE_ADD(date, unit, amount, [timezone])

Adds a specified time interval to a date.

Example DATE_ADD usage

SELECT
    id,
    item,
    orderDate as od1,
    DATE_ADD(orderDate, 'hour', 2) as od2
FROM orders
WHERE id = 2
LIMIT 1

You can also use the alias DATEADD:

DATE_SUBTRACT

DATE_SUBTRACT(date, unit, amount, [timezone])

Subtracts a specified time interval from a date.

Example DATE_SUBTRACT usage

SELECT
    id,
    item,
    orderDate as od1,
    DATE_SUBTRACT(orderDate, 'hour', 2) as od2
FROM orders
WHERE id = 2
LIMIT 1

You can also use the alias DATESUBTRACT:

DATE_DIFF

DATE_DIFF(startDate, endDate, unit, [timezone], [startOfWeek])

Calculates the difference between two dates in the specified unit.

Example DATE_DIFF usage

SELECT
    id,
    item,
    orderDate,
    CURRENT_DATE() as now,
    DATE_DIFF(orderDate, DATE_ADD(orderDate, 'day', 2), 'day') as diff
FROM orders
WHERE id = 2
LIMIT 1

You can also use the alias DATEDIFF:

DATE_TRUNC

DATE_TRUNC(date, unit)

Truncates a date to the specified unit of granularity.

Example DATE_TRUNC usage

SELECT
    orderDate,
    DATE_TRUNC(orderDate, 'month') as monthStart,
    DATE_TRUNC(orderDate, 'year') as yearStart
FROM orders
LIMIT 5

The unit parameter can be one of the following:

'year'
'quarter'
'month'
'week'
'day'
'hour'
'minute'
'second'
'millisecond'

This function is useful for grouping dates by a specific time unit or for finding the start of a time period.

Example DATE_TRUNC usage in grouping

SELECT
    DATE_TRUNC(orderDate, 'month') as monthStart,
    COUNT(*) as orderCount
FROM orders
GROUP BY DATE_TRUNC(orderDate, 'month')
ORDER BY monthStart

This query groups orders by month and counts the number of orders in each month.