Fill in the Blanks
Mastering SQL Datetime functionsOnline version
Drills to master SQL datetime functions
1
LENGTH
TRIM
SUBSTRING
CONCAT
REPLACE
Problem
1
:
String
Functions
First
,
a
brief
touch
on
essential
string
functions
that
are
particularly
useful
in
data
engineering
:
(
)
:
Merges
two
or
more
strings
into
one
.
(
)
:
Extracts
a
substring
from
a
string
.
(
)
:
Replaces
all
occurrences
of
a
substring
within
a
string
.
(
)
:
Removes
spaces
from
the
beginning
and
end
of
a
string
.
(
)
:
Returns
the
length
of
a
string
.
These
functions
help
in
cleaning
and
preparing
data
for
further
analysis
.
2
FROM
DATE
SELECT
AS
order_date
orders
AS
CAST
order_date_str
Problem
2
:
Convert
String
to
Date
Question
:
You
have
a
date
stored
as
a
string
2024
-
04
-
12
in
the
orders
table
under
the
column
order_date_str
.
Write
a
query
to
convert
this
to
a
DATE
data
type
.
Solution
:
(
)
;
SELECT
CAST
(
order_date_str
AS
DATE
)
AS
order_date
FROM
orders
;
Explanation
:
CAST
(
)
is
used
to
convert
the
string
to
a
DATE
type
.
This
is
essential
for
performing
date
-
specific
operations
that
aren't
possible
on
strings
.
3
date_string
CAST
AS
EXTRACT
FROM
AS
FROM
MONTH
AS
SELECT
date_string
CAST
month
table_name
year
YEAR
DATE
FROM
AS
EXTRACT
DATE
Problem
3
:
Extract
Year
and
Month
from
Date
String
Question
:
Given
a
DATE
string
2024
-
04
-
12
,
extract
the
year
and
month
separately
.
Solution
:
(
(
)
)
,
(
(
)
)
;
SELECT
EXTRACT
(
YEAR
FROM
CAST
(
date_string
AS
DATE
)
)
AS
year
,
EXTRACT
(
MONTH
FROM
CAST
(
date_string
AS
DATE
)
)
AS
month
FROM
table_name
;
Explanation
:
EXTRACT
(
)
function
is
used
to
pull
specific
parts
from
a
date
value
,
here
extracting
year
and
month
after
casting
the
string
to
a
date
.
4
SELECT
DATE
formatted_date
AS
table_name
date_string
FROM
AS
'DD-Mon-YY'
CAST
TO_CHAR
Problem
4
:
Date
Formatting
Question
:
Convert
a
DATE
from
YYYY
-
MM
-
DD
format
to
DD
-
Mon
-
YY
format
in
SQL
.
Solution
:
(
(
)
,
)
;
SELECT
TO_CHAR
(
CAST
(
date_string
AS
DATE
)
,
'DD
-
Mon
-
YY'
)
AS
formatted_date
FROM
table_name
;
Explanation
:
TO_CHAR
(
)
function
converts
dates
into
specified
string
formats
,
enhancing
readability
and
formatting
for
reporting
purposes
.
5
new_date
date
'3 months'
AS
'2024-04-12'
FROM
dual
INTERVAL
SELECT
Problem
5
:
Adding
Intervals
to
Dates
Question
:
Add
3
months
to
a
date
2024
-
04
-
12
.
Solution
:
+
;
SELECT
date
'2024
-
04
-
12'
+
INTERVAL
'3
months'
AS
new_date
FROM
dual
;
Explanation
:
The
INTERVAL
keyword
is
used
to
specify
a
period
to
be
added
to
a
date
,
useful
for
calculating
future
or
past
dates
relative
to
a
known
date
.
6
dual
'2024-05-15'
FROM
date
'2024-04-12'
date
days_difference
SELECT
AS
Problem
6
:
Difference
Between
Dates
Question
:
Calculate
the
number
of
days
between
two
dates
,
2024
-
04
-
12
and
2024
-
05
-
15
.
Solution
:
-
;
SELECT
date
'2024
-
05
-
15'
-
date
'2024
-
04
-
12'
AS
days_difference
FROM
dual
;
Explanation
:
Subtracting
one
date
from
another
directly
results
in
the
difference
in
days
between
them
,
crucial
for
duration
or
period
calculations
.
7
event_timestamp
AS
FROM
AS
AS
year
EXTRACT
week
HOUR
AS
SELECT
event_timestamp
event_timestamp
MINUTE
DAY
YEAR
event_timestamp
SECOND
AS
FROM
FROM
WEEK
MONTH
FROM
EXTRACT
AS
FROM
FROM
event_logs
FROM
EXTRACT
event_timestamp
event_timestamp
AS
FROM
minute
day
EXTRACT
month
EXTRACT
event_timestamp
EXTRACT
event_timestamp
hour
EXTRACT
second
Problem
7
:
Scenario
with
TIMESTAMP
rather
than
DATE
Scenario
:
Suppose
you
have
a
table
named
event_logs
with
a
column
event_timestamp
of
type
TIMESTAMP
which
stores
the
date
and
time
of
when
events
occurred
in
the
system
.
Problem
:
Extract
the
year
,
month
,
day
,
week
number
,
hour
,
minute
,
and
second
from
the
event_timestamp
for
each
event
and
display
them
along
with
the
original
timestamp
.
Solution
:
Here's
an
SQL
query
that
demonstrates
how
to
use
these
date
and
time
functions
:
,
(
)
,
(
)
,
(
)
,
(
)
,
(
)
,
(
)
,
(
)
;
SELECT
event_timestamp
,
EXTRACT
(
YEAR
FROM
event_timestamp
)
AS
year
,
EXTRACT
(
MONTH
FROM
event_timestamp
)
AS
month
,
EXTRACT
(
DAY
FROM
event_timestamp
)
AS
day
,
EXTRACT
(
WEEK
FROM
event_timestamp
)
AS
week
,
EXTRACT
(
HOUR
FROM
event_timestamp
)
AS
hour
,
EXTRACT
(
MINUTE
FROM
event_timestamp
)
AS
minute
,
EXTRACT
(
SECOND
FROM
event_timestamp
)
AS
second
FROM
event_logs
;
Explanation
:
EXTRACT
(
YEAR
FROM
event_timestamp
)
:
Extracts
the
year
from
the
event_timestamp
.
EXTRACT
(
MONTH
FROM
event_timestamp
)
:
Extracts
the
month
from
the
timestamp
.
EXTRACT
(
DAY
FROM
event_timestamp
)
:
Retrieves
the
day
of
the
month
from
the
timestamp
.
EXTRACT
(
WEEK
FROM
event_timestamp
)
:
Gets
the
week
number
of
the
year
from
the
timestamp
.
EXTRACT
(
HOUR
FROM
event_timestamp
)
:
Extracts
the
hour
of
the
day
from
the
timestamp
.
EXTRACT
(
MINUTE
FROM
event_timestamp
)
:
Retrieves
the
minute
from
the
timestamp
.
EXTRACT
(
SECOND
FROM
event_timestamp
)
:
Gets
the
second
from
the
timestamp
.
8
SELECT
EXTRACT
EXTRACT
hours
EXTRACT
EXTRACT
FROM
SELECT
end_time
FROM
FLOOR
EPOCH
EPOCH
start_time
seconds_difference
FROM
end_time
FROM
AS
seconds
end_time
EXTRACT
end_time
FROM
AS
start_time)) % 3600
FROM
60
SELECT
events
AS
FLOOR
hours_difference
start_time))/(3600
start_time)) % 60
EPOCH
EPOCH
end_time
FROM
24
AS
EPOCH
events
events
SELECT
FROM
end_time
start_time
minutes
EPOCH
AS
EXTRACT
FROM
start_time
start_time
minutes_difference
60
SELECT
3600
days_difference
events
EPOCH
AS
FROM
AS
3600
end_time
FROM
EXTRACT
Problem
8
:
Calculating
time
intervals
(
Part
1
)
Calculating
the
difference
between
dates
and
expressing
that
difference
in
various
time
units
such
as
seconds
,
minutes
,
hours
,
weeks
,
months
,
and
years
involves
using
specific
SQL
functions
and
calculations
.
Example
Scenario
:
Consider
two
TIMESTAMP
columns
start_time
and
end_time
in
a
table
named
events
.
Let's
see
how
to
compute
the
differences
in
various
units
:
Seconds
,
Minutes
,
Hours
and
Days
(
PostgreSQL
,
MySQL
,
SQL
Server
,
Oracle
)
-
-
Seconds
(
(
-
)
)
;
-
-
Minutes
(
(
-
)
)
/
;
-
-
Hours
(
(
-
)
)
/
;
-
-
Days
(
(
-
*
)
-
-
Hours
,
Minutes
,
Seconds
(
(
(
-
)
)
/
)
,
(
(
(
(
-
)
/
)
,
(
(
-
;
-
-
Seconds
SELECT
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
AS
seconds_difference
FROM
events
;
-
-
Minutes
SELECT
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
/
60
AS
minutes_difference
FROM
events
;
-
-
Hours
SELECT
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
/
3600
AS
hours_difference
FROM
events
;
-
-
Days
SELECT
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
/
(
3600
*
24
)
AS
days_difference
-
-
Hours
,
Minutes
,
Seconds
SELECT
FLOOR
(
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
/
3600
)
AS
hours
,
FLOOR
(
(
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
%
3600
)
/
60
)
AS
minutes
,
EXTRACT
(
EPOCH
FROM
(
end_time
-
start_time
)
)
%
60
AS
seconds
FROM
events
;
These
queries
utilize
the
age
(
)
function
in
PostgreSQL
,
which
computes
the
difference
between
two
timestamps
as
an
interval
type
,
and
the
EXTRACT
(
)
function
to
pull
specific
components
from
that
interval
.
The
calculations
can
be
adapted
to
other
SQL
dialects
,
but
the
functions
and
precise
syntax
may
vary
.
Each
SQL
dialect
might
have
different
nuances
in
how
it
handles
date
and
time
calculations
,
especially
for
more
complex
breakdowns
like
months
and
weeks
together
.
9
EXTRACT
start_time
DAY
start_time
SELECT
EXTRACT
FROM
FROM
events
weeks
FROM
age
FROM
start_time
end_time
EXTRACT
age
start_time)) * 12
start_time)) / 7
events
YEAR
months
weeks
age
age
SELECT
years
AS
start_time
MONTH
age
AS
AS
age
age
weeks_difference
FROM
WEEK
AS
end_time
EXTRACT
AS
WEEK
age
events
AS
start_time)) % 4
FROM
end_time
FROM
EXTRACT
YEAR
FROM
end_time
events
start_time)) * 12
age
EXTRACT
months_difference
FROM
FROM
end_time
EXTRACT
end_time
start_time
start_time
SELECT
FROM
end_time
FROM
age
FROM
months
AS
MONTH
EXTRACT
AS
SELECT
FLOOR
EXTRACT
MONTH
FROM
EXTRACT
SELECT
FROM
end_time
end_time
YEAR
YEAR
events
end_time
years_difference
Problem
9
:
Calculating
time
intervals
(
Part
2
)
Weeks
,
Months
,
Years
(
PostgreSQL
,
MySQL
,
SQL
Server
,
Oracle
)
The
calculation
of
weeks
,
months
,
and
years
often
requires
more
specific
handling
because
these
units
don't
have
a
fixed
size
(
e
.
g
.
,
months
can
have
28
to
31
days
,
years
can
have
365
or
366
days
)
.
-
-
Weeks
(
(
,
)
)
;
-
-
Months
(
(
,
+
(
(
,
)
)
;
-
-
Years
(
(
,
)
)
;
Months
and
Weeks
,
Years
,
Months
,
and
Weeks
(
PostgreSQL
Example
)
Calculating
a
combination
of
years
,
months
,
weeks
,
and
days
involves
breaking
down
the
period
piece
by
piece
.
-
-
Months
and
Weeks
(
(
,
+
(
(
,
)
)
,
(
(
,
-
-
Assuming
roughly
4
weeks
per
month
;
-
-
Years
,
Months
,
and
Weeks
(
(
,
)
)
,
(
(
,
)
)
,
(
(
(
,
)
;
-
-
Weeks
SELECT
EXTRACT
(
WEEK
FROM
age
(
end_time
,
start_time
)
)
AS
weeks_difference
FROM
events
;
-
-
Months
SELECT
EXTRACT
(
YEAR
FROM
age
(
end_time
,
start_time
)
)
*
12
+
EXTRACT
(
MONTH
FROM
age
(
end_time
,
start_time
)
)
AS
months_difference
FROM
events
;
-
-
Years
SELECT
EXTRACT
(
YEAR
FROM
age
(
end_time
,
start_time
)
)
AS
years_difference
FROM
events
;
Months
and
Weeks
,
Years
,
Months
,
and
Weeks
(
PostgreSQL
Example
)
Calculating
a
combination
of
years
,
months
,
weeks
,
and
days
involves
breaking
down
the
period
piece
by
piece
.
-
-
Months
and
Weeks
SELECT
EXTRACT
(
YEAR
FROM
age
(
end_time
,
start_time
)
)
*
12
+
EXTRACT
(
MONTH
FROM
age
(
end_time
,
start_time
)
)
AS
months
,
EXTRACT
(
WEEK
FROM
age
(
end_time
,
start_time
)
)
%
4
AS
weeks
-
-
Assuming
roughly
4
weeks
per
month
FROM
events
;
-
-
Years
,
Months
,
and
Weeks
SELECT
EXTRACT
(
YEAR
FROM
age
(
end_time
,
start_time
)
)
AS
years
,
EXTRACT
(
MONTH
FROM
age
(
end_time
,
start_time
)
)
AS
months
,
FLOOR
(
EXTRACT
(
DAY
FROM
age
(
end_time
,
start_time
)
)
/
7
)
AS
weeks
FROM
events
;
These
queries
utilize
the
age
(
)
function
in
PostgreSQL
,
which
computes
the
difference
between
two
timestamps
as
an
interval
type
,
and
the
EXTRACT
(
)
function
to
pull
specific
components
from
that
interval
.
The
calculations
can
be
adapted
to
other
SQL
dialects
,
but
the
functions
and
precise
syntax
may
vary
.
Each
SQL
dialect
might
have
different
nuances
in
how
it
handles
date
and
time
calculations
,
especially
for
more
complex
breakdowns
like
months
and
weeks
together
.
|
New game