Extract

extract returns a specified portion of a date value. Requesting a time-related portion of a date that doesn't contain time information returns 0.

Syntax

extract(period, date)

Arguments

period

The period that you want extracted from the date value. Valid periods are as follows:

• YYYY: This returns the year portion of the date.
• Q: This returns the quarter that the date belongs to (1–4).
• MM: This returns the month portion of the date.
• DD: This returns the day portion of the date.
• WD: This returns the day of the week as an integer, with Sunday as 1.
• HH: This returns the hour portion of the date.
• MI: This returns the minute portion of the date.
• SS: This returns the second portion of the date.

• MS: This returns the millisecond portion of the date.

  NOTE   Extracting milliseconds is not supported in Presto databases below version 0.216.

date

A date field or a call to another function that outputs a date.

Return type

Integer

Example

The following example extracts the day from a date value.

extract('DD', orderDate)

The following are the given field values.

orderDate
=========
01/01/14  
09/13/16        

For these field values, the following values are returned.

01
13