Date/Time Functions and Operators（3）
Tags: opengauss
Published: 2023-03-06 09:05:21

**EXTRACT**

**·** EXTRACT(field_ _FROM source)

The **extract** function retrieves subcolumns such as year or hour from date or time values. **source** must be a value expression of **timestamp**, **time**, or **interval** type. (Expressions of the **date** type are cast to **timestamp** and can therefore be used as well.) **field** is an identifier or string that specifies the columns to be extracted from the source value. The **extract** function returns values of **double precision** type. The following are valid names of **field**:

**·** century

Specifies the number of centuries.

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century numbered 0. You go from the 1st century BC to the 1st century AD.

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uz5ggh5wo9tc6nr6q7s6.png)

**·** day

**·** Specifies the date (1–31) of the month for timestamp.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pw3al23e4dtdaq41xbuo.png)

**·** Specifies the number of days for **interval**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bez5pzaq74m4bq2xxfoo.png)

**·** decade

Specifies the number of decades.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3hrjf2rln00qpbues5qr.png)

**·** dow

Specifies the day of the week. The value range from 0 (indicating Sunday) to 6 (indicating Saturday).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvmfeclufylcwvl2zvgs.png)

**·** doy

Specifies the day of the year (1–365 or 1–366).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7bj5qjn880nvtty423w1.png)

**·** epoch

**·** For **timestamp with time zone**, it specifies the number of seconds since 1970-01-01 00:00:00-00 UTC (can be negative).

For **date** and **timestamp**, it specifies the number of seconds since 1970-01-01 00:00:00-00 (local time).

For **interval**, it specifies the total number of seconds in the interval.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tz1umjw1qs49rla5ju2v.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yz3u3kw8d9bkcq5r6rfh.png)

**·** The following converts an epoch value back to a timestamp.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9a4c093s4c20ktyq58fx.png)

**·** hour

Specifies the hour column (0–23).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/87i225er7yh2p4hb0rmi.png)

**·** isodow

Specifies the day of the week (1–7).

1 indicates Monday and 7 indicates Sunday.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xlmxynz76j7ejhtr917u.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aun0x024mq77x9z3d5j8.png)

**·** isoyear

The ISO 8601 year that the date falls in (not applicable to intervals).

Each ISO year begins with the Monday of the week containing January 4, so in early January or late December the ISO year may be different from the Gregorian year. See the week column for more information.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eggrqvdn6m84l3jdz78i.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iie6xryhw2kzn8ze8dxs.png)

**·** microseconds

Specifies the second column, including fractional parts, multiplied by 1,000,000.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y5rk0miv08m2agzqdrd6.png)

**·** millennium

Specifies the number of millenniums.

Years in the 1900s are in the second millennium. The third millennium started from January 1, 2001.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qoelmu3n3mfrbw2rjgr6.png)

**·** milliseconds

Specifies the second column, including fractional parts, multiplied by 1000. Note that this includes full seconds.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8vmcsquizsxn8s426cdc.png)

**·** minute

Specifies the minute column (0–59).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jvodirehhvn8cyif2bm7.png)

**·** month

For timestamp, it specifies the month in the year (1–12).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3zlh4crvxyyrnny8wv73.png)

For interval, it specifies the number of months and modulo 12 (0–11).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/23jygp4aglw03wflr2dg.png)

**·** quarter

Specifies the quarter of the year (1–4) that the date is in.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xatdmsgxfjyh9ew1gmvg.png)

**·** second

Specifies the second column, including fractional parts (0–59).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nsl3dudbh4sxr61gsx97.png)

**·** timezone

Time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, and negative values correspond to zones west of UTC.

**·** timezone_hour

Hour part of the time zone offset.

**·** timezone_minute

Minute part of the time zone offset.

**·** week

Number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year.

Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year, and late December dates to be part of the 1st week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, 2006-01-01 is part of the 52nd week of year 2005, and 2012-12-31 is part of the 1st week of year 2013. You are advised to use the columns isoyear and week together to ensure consistency.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bp8l9ok4k9e488y1ogwl.png)

**·** year

Specifies the year column.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hpiit95315ro2gri3otu.png)