Advanced SQL

Date/Time and String Functions

Download Music Database

Music SQLite Database

The full Chinook database is optional. Examples using its shared columns transfer to it, but dates, counts, and totals differ. practice_contact and practice_event are classroom-only tables. The supplied database is already initialized.

The music-store tables

Table	Columns used	One row represents
Customer	CustomerId, FirstName, LastName, Company, Email, Phone, Country	One customer
Invoice	InvoiceId, CustomerId, InvoiceDate, Total	One purchase
Track	TrackId, Name, Composer, Milliseconds	One track
Employee	EmployeeId, FirstName, LastName, BirthDate, HireDate	One employee

Join Invoice.CustomerId to Customer.CustomerId.

Classroom InvoiceDate values use UTC, with format YYYY-MM-DD HH:MM:SS.

Tables for deliberately messy data

Table	Columns	Purpose
practice_contact	contact_id, first_name, last_name, email, phone	Spaces, blanks, NULLs, mixed case, malformed email
practice_event	event_id, label, start_text, end_text	Offsets, missing endpoints, invalid input, reversed intervals

A timestamp without an offset in these exercises is assumed UTC.

Keep the raw values. Derive cleaned columns with SELECT so you can compare before and after.

Strings

• Strings

• Dates and times

• Activity Solutions

Text literals and identifiers

SELECT 'Ada' AS first_name,
       'O''Brien' AS last_name,
       'Data 300' AS "course label";

1

Single quotes create a text value.

2

Two adjacent apostrophes represent one apostrophe.

3

Double quotes delimit an identifier, including an alias with a space.

NULL, '', and 'NULL' are three different values.

Text, numbers, and missing values

SELECT typeof('007') AS text_type,
       typeof(7) AS number_type,
       length('') AS empty_length,
       length(NULL) AS missing_length;

Concatenation with ||

SELECT CustomerId,
       FirstName || ' ' || LastName AS customer_name
FROM Customer
ORDER BY CustomerId;

1

|| joins text expressions in the order written.

concat() and concat_ws() · SQLite 3.44+

SELECT concat('Ana', NULL, 'Rivera') AS joined,
       concat_ws(' ', 'Ana', NULL, 'Rivera') AS spaced;

joined	spaced
AnaRivera	Ana Rivera

• These functions skip NULL values among the values being joined.
• concat_ws() places the separator between retained values.
• Empty strings are retained and can create extra separators.
• All NULL value arguments give ''. A NULL separator gives NULL.

Case conversion and Unicode

SELECT lower('DATA 300') AS lower_case,
       upper('Data 300') AS upper_case,
       upper('é') AS accented_letter;

lower() and upper() handle ASCII case conversion by default. They do not provide full Unicode case folding or accent removal.

How long is a string?

SELECT length('café') AS code_points;

• length(TEXT) counts Unicode code points before the first NUL character.

Extracting with substr()

SELECT substr('SQLite', 1, 3) AS first_three,
       substr('SQLite', 4) AS from_four,
       substr('SQLite', -3) AS last_three;

1

Start at position 1 and take three characters: SQL.

2

Omit the length to continue to the end: ite.

3

A negative start counts from the right: ite.

substring() is an alias in SQLite 3.34+.

Finding a delimiter with instr()

SELECT instr('ana@example.edu', '@') AS at_position,
       instr('ana.example.edu', '@') AS missing_at;

instr(text, search) returns the position of the first match. It returns 0 when there is no match, and NULL if an argument is NULL.

trim(), ltrim(), and rtrim()

SELECT trim('  Ana  ') AS both_ends,
       ltrim('  Ana  ') AS left_only,
       rtrim('  Ana  ') AS right_only;

Activity

Table: practice_contact

Return one row per contact with:

1. contact_id
2. display_name: trim both names, join with one space, and avoid a trailing space when the last name is NULL.
3. email_clean: lowercase and trim the email
4. Remove rows with missing emails.

LIKE pattern

SELECT TrackId, Name
FROM Track
WHERE Name LIKE '%love%'
ORDER BY TrackId;

Default LIKE ignores ASCII letter case, but does not fully fold non-ASCII case.

LIKE patterns

Pattern	Meaning
'Love%'	Starts with Love
'%Love%'	Contains Love
'%Love'	Ends with Love

Searching for literal % and _

SELECT TrackId, Name
FROM Track
WHERE Name LIKE '%!%%' ESCAPE '!'
   OR Name LIKE '%!_%' ESCAPE '!'
ORDER BY TrackId;

1

!% means a literal percent sign. The outer % symbols remain wildcards.

2

!_ means a literal underscore.

Activity

Write separate queries for tracks whose names contain love fromt the Track table. Display the TrackID and Name, the length of the name in a new column, and the duration of the track in minutes from Milliseconds. Order by descending Milliseconds and TrackID.

Combining rows with group_concat()

SELECT Country,
       group_concat(FirstName, ', ') AS customers
FROM Customer
GROUP BY Country
ORDER BY Country;

1

Join the non-NULL values from multiple rows into one string per group.

Without an explicit input order, the order inside the string is not guaranteed.

group_concat(DISTINCT Country) removes duplicates with the default comma separator. Deduplicate in a CTE when you also need a custom separator.

Activity

For each country from the table Customer, return:

1. The country.
2. The number of customers.
3. A comma-separated list of customers’ full names.

Sort the countries alphabetically. On SQLite 3.44+, also order names by last name, first name, and customer ID inside the list.

Dates and times

• Strings

• Dates and times

• Activity Solutions

A date, a timestamp, and a duration

Concept	Example	Interpretation
Calendar date	2024-02-29	A day on the calendar
Local date and time	2024-03-01 09:00:00	Needs a time-zone convention
Timestamp with offset	2024-03-01T09:00:00-08:00	An instant with an explicit offset
Duration	5400 seconds	Elapsed time between instants

SQLite has no dedicated date/time storage class

Representation	Example	Useful for
TEXT	'2024-01-01 00:00:00'	Readable timestamps in a fixed format
INTEGER	1704067200	Unix seconds since 1970-01-01 UTC
REAL	2460310.5	Julian day number and fractional days

Declaring a column DATE or DATETIME does not automatically parse or validate every value inserted into it.

Choose one representation, precision, and time-zone policy per column.

Supported input conventions

SELECT datetime('2024-03-01 09:30:00') AS spaced,
       datetime('2024-03-01T09:30:00Z') AS utc_text,
       datetime('2024-03-01T09:30:00-08:00') AS offset_text;

• The first two give 2024-03-01 09:30:00.

• The third gives 2024-03-01 17:30:00.

Use supported, zero-padded year-month-day forms. SQLite does not parse every ISO 8601 variant or arbitrary locale format such as 03/01/2024.

Reformatting a documented import layout

WITH imported(raw_date) AS (VALUES ('03/25/2024'))
SELECT raw_date,
       substr(raw_date, 7, 4) || '-' ||
       substr(raw_date, 1, 2) || '-' ||
       substr(raw_date, 4, 2) AS candidate_iso_date
FROM imported;

This produces 2024-03-25 only because the source contract specifies MM/DD/YYYY with fixed widths.

The date/time function family

Function	Result type	Example use
date()	TEXT	Calendar date
time()	TEXT	Time of day
datetime()	TEXT	Date and time
strftime()	TEXT	Custom format or date component
julianday()	REAL	Fractional-day arithmetic
unixepoch()	INTEGER, or REAL with subsec	Elapsed seconds
timediff()	TEXT	Calendar-aware difference, 3.43+

The first six accept modifiers. timediff(A, B) takes exactly two time values.

Extracting a date and a time

SELECT InvoiceId,
       date(InvoiceDate) AS invoice_day,
       time(InvoiceDate) AS invoice_time
FROM Invoice
ORDER BY InvoiceId
LIMIT 3;

1

Produce a normalized YYYY-MM-DD date.

2

Produce an HH:MM:SS time of day.

These expressions return derived values. They do not change InvoiceDate.

The current moment

SELECT CURRENT_DATE AS today_utc,
       CURRENT_TIME AS time_utc,
       CURRENT_TIMESTAMP AS timestamp_utc,
       datetime('now') AS also_utc;

• The current-time keywords take no parentheses.
• 'now' uses UTC and depends on when the query runs.
• Repeated 'now' calls within the same SQLite step use the same instant.

Unix seconds: explicit conversions

SELECT unixepoch('2024-01-01 00:00:00') AS seconds,
       datetime(1704067200, 'unixepoch') AS timestamp;

1

Convert a timestamp to integer Unix seconds: 1704067200.

2

Interpret the number as Unix seconds to reconstruct the timestamp.

Activity

Use 2024-01-01 00:00:00.

1. Return its date, Julian day number, and Unix seconds.
2. Convert the Unix seconds back with datetime().

strftime(): formatting and extraction

SELECT InvoiceId,
       strftime('%Y-%m', InvoiceDate) AS year_month,
       strftime('%w', InvoiceDate) AS weekday_code,
       CAST(strftime('%H', InvoiceDate) AS INTEGER) AS hour
FROM Invoice
ORDER BY InvoiceId;

1

A sortable year-month grouping key.

2

Weekday code, with Sunday = 0 and Saturday = 6.

3

CAST: Convert the text component to a number when numeric operations need it.

%m means month. %M means minute.

Common strftime() codes

Code	Meaning	Example
%Y, %m, %d	Year, month, day	2024, 02, 29
%H, %M, %S	Hour, minute, second	09, 05, 07
%f	Seconds including fraction	07.125
%j	Day of year	060
%w	Weekday, Sunday = 0	4
%W	Week number, Monday-based, 00–53	09
%s, %J	Unix seconds, Julian day as text	Numeric-looking text
%%	Literal percent sign	%

An unsupported format code makes the result NULL. Month and weekday names need a mapping such as CASE or a lookup table.

ISO weeks can belong to a different year · 3.46+

SELECT strftime('%Y', '2021-01-01') AS calendar_year,
       strftime('%G-W%V', '2021-01-01') AS iso_week;

calendar_year	iso_week
2021	2020-W53

Use %G with %V for an ISO week-year key. Combining %Y with %V can mislabel weeks near New Year.

%W and %U are different week-numbering systems and can include week 00.

Modifiers transform a time value

SELECT date('2024-02-28', '+1 day') AS next_day,
       datetime('2024-03-01 09:30:00', '+90 minutes') AS later,
       date('2024-03-15', 'start of month') AS month_start;

Useful modifiers include signed days, hours, minutes, seconds, months, and years, plus start of day, start of month, and start of year.

Multiple modifiers can be added per function. Modifiers apply from left to right.

Finding the next weekday

SELECT date('2024-03-01', 'weekday 1') AS monday,
       date('2024-03-14', 'weekday 1') AS already_monday;

For the portable weekday N pattern, use 0 = Sunday through 6 = Saturday. It advances to that weekday or stays on the date if it already matches.

It does not automatically skip holidays. Business-day schedules need a calendar table with explicit holiday rules.

Half-open date ranges

SELECT InvoiceId, InvoiceDate, Total
FROM Invoice
WHERE InvoiceDate >= '2024-02-01 00:00:00'
  AND InvoiceDate <  '2024-03-01 00:00:00'
ORDER BY InvoiceDate, InvoiceId;

1

Include the lower boundary.

2

Exclude the next period’s lower boundary.

This includes every February timestamp, including leap day. It avoids guessing the final second or fractional second of the month.

Activity

1. Return all invoices in March 2024 using a half-open range.
2. Add due_date, exactly 14 calendar days after the invoice date.
3. Add invoice_month_end, the final date of the invoice’s month.
4. Sort by timestamp, then invoice ID.

Elapsed time in days and seconds

SELECT julianday('2024-02-29 01:00:00')
       - julianday('2024-02-28 23:30:00') AS days,
       unixepoch('2024-02-29 01:00:00')
       - unixepoch('2024-02-28 23:30:00') AS seconds;

1

The elapsed interval is 0.0625 days.

2

The same interval is 5400 seconds, or 90 minutes.

Subtract end minus start. Divide seconds by 60.0, 3600.0, or 86400.0 to preserve fractional units.

Activity

Using the Invoice table, find the time elapse from a customers first InvoiceDate to the last InvoiceDate customer 1 and 2.

Activity Solutions

• Strings

• Dates and times

• Activity Solutions

Solution A

SELECT contact_id, 
    trim(first_name) || ' ' || trim(last_name) as display_name,
    trim(lower(email)) as email_clean
FROM practice_contact
WHERE display_name is not null
ORDER BY contact_id;

Solution B

SELECT TrackId, Name,
       substr(Name, 1, 12) AS name_preview,
       length(Name) AS name_length,
       round(Milliseconds / 60000.0, 2) AS duration_minutes
FROM Track
WHERE Name LIKE '%love%'
ORDER BY Milliseconds DESC, TrackId;

Solution C

With cc as (
    select *,
         FirstName || ' ' || LastName as full_name,
         count(*) over (PARTITION BY Country) as number_of_customers
    from Customer
    )
SELECT Country, number_of_customers,
       group_concat(full_name, ', ') AS customers
FROM cc
GROUP BY Country
ORDER BY Country;

Solution D · Billing and reminder dates

WITH new(raw_date) AS (VALUES ('2024-01-01 00:00:00'))
SELECT raw_date,
        date(raw_date) as real_date,
        julianday(raw_date) as julian,
        unixepoch(raw_date) as unix
FROM new;

Solution E

SELECT InvoiceId, InvoiceDate, Total,
    date(InvoiceDate, '+14 day') as due_date,
    date(InvoiceDate, 'start of month', '+1 month', '-1 day') as invoice_month_end,
    time(InvoiceDate) as timestamp
FROM Invoice
WHERE InvoiceDate >= '2024-03-01 00:00:00'
  AND InvoiceDate <  '2024-04-01 00:00:00'
ORDER BY InvoiceDate, InvoiceId;

Solution F

SELECT CustomerId,
       MIN(InvoiceDate) AS first_invoice_date,
       MAX(InvoiceDate) AS last_invoice_date,
       julianday(MAX(InvoiceDate))
           - julianday(MIN(InvoiceDate)) AS elapsed_days
FROM Invoice
WHERE CustomerId IN (1, 2)
GROUP BY CustomerId
ORDER BY CustomerId;