SQL EXTRACT() and Python ORD()/CHR()

A useful function in SQL

Today I was trying to retrieve the ‘year’ information from a date object in SQL and learned about this EXTRACT() function. Below is an example usage in which the ‘year’ is identified from a date string.

SELECT EXTRACT(YEAR FROM '2024-03-18'); -- output: 2024

It turns out the function is very powerful in retrieving various time components from a date or datetime object. Below is the list of possible time components:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Here are another two sample usages:

SELECT EXTRACT(MINUTE FROM "2023-03-18 22:34:21"); -- output: 34

SELECT EXTRACT(DAY_HOUR FROM "2023-03-18 22:34:21"); -- output: 1822

Two functions in Python

There is always a handy function in Python when you need it and here is the pair of functions, ord() and chr(), that converts a character into a number and converts the number back. Their official documentation can be found here and here.

char = 'a'
print(ord(char))

97

num = ord(char)
print(chr(num))

a