PostgreSQL - EXTRACT Function

The EXTRACT function in PostgreSQL is used to retrieve subfields like year, month, day, etc., from a date or time value. It's quite useful when you want to isolate a specific component of a date or time.

Syntax:

EXTRACT(field FROM source)

field: Represents the part of the date/time value you want to extract. This can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
source: The date, time, timestamp, or interval value from which to extract the field.

Examples:

Extracting the Year from a Date:
```
SELECT EXTRACT(YEAR FROM '2023-07-15'::date);
```
This would return 2023.
Extracting the Month from a Date:
```
SELECT EXTRACT(MONTH FROM '2023-07-15'::date);
```
This would return 7.
Extracting the Day from a Date:
```
SELECT EXTRACT(DAY FROM '2023-07-15'::date);
```
This would return 15.

Extracting Hour, Minute, and Second from a Timestamp:

SELECT 
  EXTRACT(HOUR FROM '2023-07-15 14:30:45'::timestamp) AS Hour,
  EXTRACT(MINUTE FROM '2023-07-15 14:30:45'::timestamp) AS Minute,
  EXTRACT(SECOND FROM '2023-07-15 14:30:45'::timestamp) AS Second;

This would return 14, 30, and 45 respectively for Hour, Minute, and Second.

Extracting Day of the Week:
```
SELECT EXTRACT(DOW FROM '2023-07-15'::date);
```
This would return 6 because, by default, PostgreSQL considers Sunday as 0 and Saturday as 6.

Points to Remember:

The result of the EXTRACT function is always returned as a double precision number. For most fields, the result will be a whole number. However, for fields like SECOND, it can be fractional.
The date or time value you're extracting from doesn't need to be a constant. It can be a column in a table or the result of another function.

In summary, the EXTRACT function is a versatile tool in PostgreSQL for isolating specific components from date and time values, and it's frequently used in data analysis and processing tasks that require date and time manipulation.

PostgreSQL EXTRACT Function example:
- Description: The EXTRACT function in PostgreSQL is used to extract parts of a date or time.
- Code:
```
SELECT EXTRACT(field FROM source);
```
How to use EXTRACT Function in PostgreSQL:
- Description: Use the EXTRACT function to retrieve specific components (year, month, day, etc.) from a date or time.
- Code:
```
SELECT EXTRACT(YEAR FROM '2022-01-01'::DATE) AS extracted_year;
```

PostgreSQL EXTRACT Function date part:

Description: Extract the date part (year, month, day) using EXTRACT.

Code:

SELECT EXTRACT(YEAR FROM '2022-01-01'::DATE) AS extracted_year,
       EXTRACT(MONTH FROM '2022-01-01'::DATE) AS extracted_month,
       EXTRACT(DAY FROM '2022-01-01'::DATE) AS extracted_day;

Extracting year from date in PostgreSQL:
- Description: Retrieve the year from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(YEAR FROM '2022-01-01'::DATE) AS extracted_year;
```
Month extraction using EXTRACT in PostgreSQL:
- Description: Extract the month from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(MONTH FROM '2022-01-01'::DATE) AS extracted_month;
```

PostgreSQL EXTRACT Function time part:

Description: Extract the time part (hour, minute, second) using EXTRACT.

Code:

SELECT EXTRACT(HOUR FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_hour,
       EXTRACT(MINUTE FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_minute,
       EXTRACT(SECOND FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_second;

Day extraction with EXTRACT in PostgreSQL:
- Description: Extract the day from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(DAY FROM '2022-01-01'::DATE) AS extracted_day;
```

Using EXTRACT for timestamp in PostgreSQL:

Description: Extract components (year, month, day, etc.) from a timestamp using the EXTRACT function.

Code:

SELECT EXTRACT(YEAR FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_year,
       EXTRACT(MONTH FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_month,
       EXTRACT(DAY FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_day,
       EXTRACT(HOUR FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_hour,
       EXTRACT(MINUTE FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_minute,
       EXTRACT(SECOND FROM '2022-01-01 12:30:45'::TIMESTAMP) AS extracted_second;

Quarterly extraction in PostgreSQL:
- Description: Extract the quarter from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(QUARTER FROM '2022-01-01'::DATE) AS extracted_quarter;
```

Extracting timezone information with EXTRACT in PostgreSQL:

Description: Extract the timezone offset from a timestamp with the EXTRACT function.

Code:

SELECT EXTRACT(TIMEZONE_HOUR FROM '2022-01-01 12:30:45-05'::TIMESTAMPTZ) AS tz_hour,
       EXTRACT(TIMEZONE_MINUTE FROM '2022-01-01 12:30:45-05'::TIMESTAMPTZ) AS tz_minute;

PostgreSQL EXTRACT Function timezone conversion:
- Description: Use the AT TIME ZONE clause to convert timestamps to a different timezone.
- Code:
```
SELECT '2022-01-01 12:30:45'::TIMESTAMP AT TIME ZONE 'UTC' AS converted_utc_time;
```
Calculating age using EXTRACT in PostgreSQL:
- Description: Calculate the age in years using the EXTRACT function.
- Code:
```
SELECT EXTRACT(YEAR FROM AGE('2022-01-01'::DATE)) AS age_years;
```
Week extraction using EXTRACT in PostgreSQL:
- Description: Extract the week from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(WEEK FROM '2022-01-01'::DATE) AS extracted_week;
```
Extracting century in PostgreSQL:
- Description: Extract the century from a date using the EXTRACT function.
- Code:
```
SELECT EXTRACT(CENTURY FROM '2022-01-01'::DATE) AS extracted_century;
```

PostgreSQL EXTRACT Function interval usage:

Description: Extract components (years, months, days, etc.) from an interval using the EXTRACT function.

Code:

SELECT EXTRACT(YEAR FROM INTERVAL '2 years 3 months') AS extracted_years,
       EXTRACT(MONTH FROM INTERVAL '2 years 3 months') AS extracted_months;