SQL extract — Get a Field from a Date or Time Value


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.

Meaningextract field
YearYEAR
MonthMONTH
Day of monthDAY
24 hourHOUR
MinuteMINUTE
Seconds (including fractions)SECOND
Time zone hourTIMEZONE_HOUR
Time zone minuteTIMEZONE_MINUTE

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.

Caution: Oracle Database

The Oracle database doesn’t have a predefined date type without time components. Even the Oracle type date has a time component—in this regard, Oracle date is more like the standard timestamp.2 A type conversion (cast) to date therefore does not drop the time component in the Oracle database.

To use the date only—without time components—it is common practice to use the proprietary trunc function to set all time fields to zero (0):

trunc(<timestamp>)

Note that the result still has the time components—they are just set to zero. The effect is basically like the following cast expression in standard SQL:

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.

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.

On my Own Behalf

If you like this article, you might also like my book SQL Performance Explained or my training.

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.

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)

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

Footnotes

0

SQL:2016, Part 2, §6.30, Syntax Rule 7

1

SQL:2016, Part 2, §6.13, General Rule 14c-d for date, SQL:2016, Part 2, §6.13, General Rule 16d-e and 17d-e for time.

3

The leap second appears at different times in different time zones (but always 23:59:60 UTC). Generally speaking, you cannot assume that the second 59 is the last second of every minute.

4

This is the time in which Douglas Noel Adams was aged 42 years.

5

It’s “mostly useless” because the database can still use this index column as index filter predicate (in the worst case during a full index scan). However, that’s “mostly useless” compared to an index access predicate.

6

“Proprietary” from the SQL standards perspective. strftime is still standardized, not by SQL but by POSIX.

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license