Timestamps and EXTRACT

PostgreSQL can hold date and time information.

TIME - Contains only time
DATE - Contains only date
TIMESTAMP - Contains date and time
TIMESTAMPTZ - Contains date, time, and timezone





EXTRACT() will retrieve a sub-component of a date value

  • YEAR
  • MONTH
  • DAY
  • WEEK
  • QUARTER



AGE() returns the amount of time that has passed since the timestamp was created.


TO_CHAR() will convert data types to text.

https://www.postgresql.org/docs/13/functions-formatting.html

Pattern Description
HH hour of day (01–12)
HH12 hour of day (01–12)
HH24 hour of day (00–23)
MI minute (00–59)
SS second (00–59)
MS millisecond (000–999)
US microsecond (000000–999999)
FF1 tenth of second (0–9)
FF2 hundredth of second (00–99)
FF3 millisecond (000–999)
FF4 tenth of a millisecond (0000–9999)
FF5 hundredth of a millisecond (00000–99999)
FF6 microsecond (000000–999999)
SSSS, SSSSS seconds past midnight (0–86399)
AM, am, PM or pm meridiem indicator (without periods)
A.M., a.m., P.M. or p.m. meridiem indicator (with periods)
Y,YYY year (4 or more digits) with comma
YYYY year (4 or more digits)
YYY last 3 digits of year
YY last 2 digits of year
Y last digit of year
IYYY ISO 8601 week-numbering year (4 or more digits)
IYY last 3 digits of ISO 8601 week-numbering year
IY last 2 digits of ISO 8601 week-numbering year
I last digit of ISO 8601 week-numbering year
BC, bc, AD or ad era indicator (without periods)
B.C., b.c., A.D. or a.d. era indicator (with periods)
MONTH full upper case month name (blank-padded to 9 chars)
Month full capitalized month name (blank-padded to 9 chars)
month full lower case month name (blank-padded to 9 chars)
MON abbreviated upper case month name (3 chars in English, localized lengths vary)
Mon abbreviated capitalized month name (3 chars in English, localized lengths vary)
mon abbreviated lower case month name (3 chars in English, localized lengths vary)
MM month number (01–12)
DAY full upper case day name (blank-padded to 9 chars)
Day full capitalized day name (blank-padded to 9 chars)
day full lower case day name (blank-padded to 9 chars)
DY abbreviated upper case day name (3 chars in English, localized lengths vary)
Dy abbreviated capitalized day name (3 chars in English, localized lengths vary)
dy abbreviated lower case day name (3 chars in English, localized lengths vary)
DDD day of year (001–366)
IDDD day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)
DD day of month (01–31)
D day of the week, Sunday (1) to Saturday (7)
ID ISO 8601 day of the week, Monday (1) to Sunday (7)
W week of month (1–5) (the first week starts on the first day of the month)
WW week number of year (1–53) (the first week starts on the first day of the year)
IW week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)
CC century (2 digits) (the twenty-first century starts on 2001-01-01)
J Julian Day (integer days since November 24, 4714 BC at midnight UTC)
Q quarter
RM month in upper case Roman numerals (I–XII; I=January)
rm month in lower case Roman numerals (i–xii; i=January)
TZ upper case time-zone abbreviation (only supported in to_char)
tz lower case time-zone abbreviation (only supported in to_char)
TZH time-zone hours
TZM time-zone minutes
OF time-zone offset from UTC (only supported in to_char)



Mathematical Functions

Most common mathematical functions are available. See the documentation:

https://www.postgresql.org/docs/current/functions-math.html

String Functions

There are also many string functions:

https://www.postgresql.org/docs/current/functions-string.html




SubQuery

A sub query is exactly what it sounds like - A query within a query.



The EXISTS operator is used to test for the existence of rows in a subquery.

Self-Join

A self-join is a query in which a table is joined to itself.