extract

SQL extract provides access to the components of temporal data types—i.e. date, time, timestamp, and interval.

SQL extract uses the keyword from to separate the field name from the value.

EXTRACT(<field> FROM <expression>)

The field names are SQL keywords too—you must not put them in double or single quotes.

SQL extract returns an exact numeric value. For second, it also includes fractions.0 The following table lists the extract fields defined by the SQL standard.

Related Features

Extract can only get single fields. To extract the full date (year, month, day) or time (hour, minute, second) from a timestamp, cast can be used:1

CAST(<timestamp> AS [DATE|TIME])

This is particularly useful for the group by clause. In the where clause, it is often the wrong choice. For more on this, see “Inappropriate Use in The Where Clause” below.

trunc(<timestamp>)

CAST(CAST(<timestamp> AS DATE) AS TIMESTAMP)

Compatibility

SQL extract was available in SQL-92 (intermediate) and is now part of the optional feature F052, “Intervals and datetime arithmetic”. Despite its maturity and relevance, extract is still not supported by all major databases yet.

1. No time zone fields • SECOND does not include fractions
2. No time zone fields
3. No time zone fields • SECOND does not include fractions. Use SECOND_MICROSECOND
4. Doesn’t support data type interval
5. See “Caution: Oracle Database” above
6. Use date(<timestamp>) instead
7. Use time(<timestamp>) instead

Related Anti-Patterns

String Formatting Functions

A very common anti-pattern is to use string formatting functions (e.g. to_char) instead of extract to get single date or time fields. These string function often apply unintended formatting such as leading spaces or zeros, or a comma (,) instead of a period (.) as decimal mark based on the current locale.

This environmentally dependent behavior can lead to bugs that don’t show up in all environments and are thus hard to correct.

Inappropriate Use in The Where Clause

Consider the following anti-pattern:

WHERE EXTRACT(YEAR FROM some_date) = 2016

This anti-pattern is often followed to avoid specifying the “last moment of” the relevant time frame. This is, in fact, an important and desirable goal because specifying the “last moment of” is actually impossible:

Time units are not uniform

It is well known that the length of a month is not uniform. The rules for leap years are known at least in part. Just considering these facts, any “last moment of” could be determined algorithmically.

But there are also leap seconds, which are irregular. They are inserted occasionally on demand. For example, the last UTC second of the year 2016 happened to be 23:59:60. If you consider a day to end at 23:59:59 UTC, you might miss a whole second.3

Due to the irregularity and the rather short lead time when it comes to leap seconds insertions—the 2016 leap second was announced less than six month in advance—it is impossible to tell the last moment of a month for more than six month in advance.

In addition to this more or less theoretic special case, it is also good to avoid the need to specify the “last moment of” because it is rather awkward to calculate.

The time component’s resolution is unknown (at least in the future)

Even if you have correctly determined the last day and last second of a period, you might need to include a sufficient number of fractional digits to specify the “last moment of” a period. If you know that the type of the relevant column does not allow fractions (e.g., timestamp(0)), you don’t need to consider any fractions right now. But if the type is changed to timestamp(6) later, chances are the “last moment of” assumptions are not updated.

It is therefore a very good practice to avoid using the “last moment of”. Using extract, cast, or string formatting functions is just the wrong approach to reaching that goal.

The following where clause is equivalent to the extract example from above and still avoids specifying the “last moment of” the year 2016:

WHERE some_date >= DATE'2016-01-01'
  AND some_date <  DATE'2017-01-01'

Note the pattern: use an inclusive comparison (>=) for the lower bound but an excluding comparison (<) for the upper bound. Consequently you need to specify the first moment to be excluded from the result as the upper bound. The inclusive/exclusive pattern avoids the need to specify the “last moment of” the relevant time frame by using the less troublesome “first moment of” twice.

Note that SQL’s between cannot be used for this pattern because between includes both boundary values.

Compared to the extract solution, the inclusive/exclusive condition has two advantages:

It works for arbitrary time frames

You can easily select a single month, day, …—even if it is not aligned to the calendar. Consider how you would implement the following example using extract, to_char, or something similar:4

WHERE some_date >= DATE'1994-03-11'
  AND some_date <  DATE'1995-03-11'

It can use an index on the date/time column

An index on some_date is mostly useless if the where clause wraps the indexed columns through a function or expression like extract.5 The explicit inclusive/exclusive pattern can make use of such an index. Learn more about indexing use at Use The Index, Luke!

Proprietary Extensions: Additional Fields

Some databases support further extract fields. The following table summarizes the more commonly available proprietary extract fields. Please note that these are proprietary extensions: they may behave differently from product to product. The field week, for example, works in three tested database, but returns a different result for each of them.

1. Sunday = 1
2. Sunday = 0
3. Sunday-based weeks with first Sunday in year • Use extract(isoyear) for the corresponding year
4. ISO 8601 week date (year’s first Thursday)
5. Sunday-based weeks with first Sunday in year
6. ISO 8601 week date (year’s first Thursday) • Use extract(isoyear) for the corresponding year
7. Without seconds: 12.3456789 seconds is returned as 3456789
8. Includes seconds: 12.3456789 seconds is returned as 123456789
9. Including fractions

Proprietary Alternatives

Most databases offer enough functionality to get the same result as the standard extract expression. For those databases that do not (fully) support extract, you can find the proprietary alternative below.

datepart: SQL Server

Microsoft SQL Server offers the proprietary datepart function. The following example is equivalent to extract(year from <datetime>).

DATEPART(year, <datetime>)

The return type is always an integer. Fractions of seconds can be retrieved as separate fields (e.g. millisecond).

The following expression behaves like extract(second from <datetime>) with up to nine fractional digits:

  DATEPART(second , <datetime>) 
+ CAST(DATEPART(nanosecond, <datetime>)
    AS NUMERIC(9,0)
      )/power(10,9)

See “DATEPART (Transact-SQL)” for the full list of available fields.

strftime — SQLite

SQLite offers the strftime function to format dates and times as strings.6 To extract a single component, just format that component and cast it to a numeric type if needed. The following example is equivalent to extract(year from <datetime>).

CAST(STRFTIME ('%Y', <datetime>) AS NUMERIC)

Note that the format string '%S' (for seconds) does not include fractions. Use '%f' instead (seconds including three fractional digits):

CAST(STRFTIME ('%f', <datetime>) AS NUMERIC)

extract(second_microsecond …) — MySQL, MariaDB

MySQL’s extract and MariaDB’s extract both always return integer values. To get the seconds with fractions, use the proprietary second_microsecond extract field:

EXTRACT(second_microsecond FROM <datetime>)/power(10,6)