How to emulate date_part() function in SQLite

In PostgreSQL, the date_part(field, source) function lets you easily extract specific parts of a date or timestamp, like the year, month, day, hour, minute, and so on.

Example:

SELECT date_part('year', created_at) AS year
FROM users

But in SQLite, there is no date_part() function. No worries! We can recreate this magic using the strftime() function available in SQLite.

Let’s dive into it.

strftime() syntax

In SQLite, you can emulate date_part() like this:

SELECT strftime(format_string, date_column)
FROM your_table

Here’s the idea:

• format_string is a string that describes the formatting of the date/timestamp. You can use values like %Y (for year) or %m (for month).
• date_column is a date/timestamp column name or a value of type date/timestamp

Let’s learn about strftime() first.

strftime() in action

Let’s see how the strftime() function works with a constant value. In that case, we don’t even need a table to write a query:

SELECT strftime('%Y', '2024-04-27')

Result:

Here are some useful format codes:

Here’s a more comple example that prints out date and time of a timestamp:

SELECT
  strftime('%Y-%m-%d %H:%M', created_at)
FROM users
LIMIT 3

Emulating date_part()

As you can see, the strftime() function offers way more functionality that date_part(). Emulating date_part() boils down to formatting a specific part of the timestamp. Let’s look at some examples.

One thing to keep in mind — date_part() function returns a numeric value, but the strftime() always returns a string.

Extracting year

PostgreSQL:

SELECT date_part('year', created_at) AS year
FROM users

SQLite:

SELECT strftime('%Y', created_at) AS year
FROM users

Extracting month

PostgreSQL:

SELECT date_part('month', created_at) AS month
FROM purchases

SQLite:

SELECT strftime('%m', created_at) AS month
FROM purchases

Extracting day

PostgreSQL:

SELECT date_part('day', signup_date) AS day
FROM users

SQLite:

SELECT strftime('%d', signup_date) AS day
FROM users

Extracting hour

PostgreSQL:

SELECT date_part('hour', created_at) AS hour
FROM web_analytics.pageviews

SQLite:

SELECT strftime('%H', created_at) AS hour
FROM web_analytics.pageviews

Extracting minute

PostgreSQL:

SELECT date_part('minute', created_at) AS minute
FROM mobile_analytics.events

SQLite:

SELECT strftime('%M', created_at) AS minute
FROM mobile_analytics.events

Quick Recap

Even though SQLite doesn’t have date_part() built-in like PostgreSQL, you can easily mimic it with strftime() and the right format codes.

Pretty cool, right?

You might also want to check out how to emulate date_trunc() in SQLite for rounding timestamps instead of extracting parts.