Compléter
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
orders
SELECT
DATE
FROM
AS
AS
order_date
order_date_str
CAST
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
AS
FROM
table_name
CAST
date_string
DATE
AS
AS
EXTRACT
FROM
EXTRACT
SELECT
DATE
YEAR
CAST
FROM
year
AS
date_string
month
MONTH
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
'DD-Mon-YY'
AS
DATE
SELECT
table_name
TO_CHAR
formatted_date
FROM
date_string
AS
CAST
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
'3 months'
FROM
dual
AS
'2024-04-12'
INTERVAL
date
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
date
FROM
'2024-04-12'
'2024-05-15'
days_difference
dual
date
AS
SELECT
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
year
AS
FROM
event_timestamp
event_timestamp
EXTRACT
EXTRACT
AS
HOUR
EXTRACT
event_logs
event_timestamp
week
SELECT
FROM
FROM
MINUTE
DAY
AS
EXTRACT
FROM
minute
second
event_timestamp
day
event_timestamp
FROM
event_timestamp
YEAR
month
AS
event_timestamp
AS
hour
EXTRACT
EXTRACT
WEEK
FROM
SECOND
EXTRACT
FROM
FROM
MONTH
AS
event_timestamp
AS
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
end_time
EPOCH
FROM
AS
AS
60
minutes_difference
FROM
days_difference
EXTRACT
end_time
seconds
EPOCH
start_time
events
FROM
EXTRACT
seconds_difference
3600
EPOCH
AS
EXTRACT
events
FROM
start_time)) % 3600
start_time
EPOCH
EXTRACT
FROM
24
SELECT
60
start_time)) % 60
FROM
FLOOR
start_time))/(3600
EXTRACT
end_time
EXTRACT
hours_difference
SELECT
EXTRACT
end_time
FROM
AS
SELECT
end_time
events
events
FROM
AS
AS
start_time
SELECT
FLOOR
end_time
minutes
start_time
AS
hours
EPOCH
EPOCH
FROM
3600
FROM
end_time
SELECT
FROM
EPOCH
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
FROM
end_time
age
SELECT
FROM
start_time
FROM
end_time
AS
start_time
EXTRACT
end_time
EXTRACT
end_time
EXTRACT
start_time
age
weeks
WEEK
YEAR
EXTRACT
AS
age
AS
SELECT
start_time)) * 12
events
EXTRACT
AS
months
months
AS
AS
YEAR
FROM
weeks_difference
FROM
FROM
EXTRACT
FROM
FROM
age
events
age
start_time)) % 4
EXTRACT
YEAR
MONTH
WEEK
FROM
age
EXTRACT
end_time
age
SELECT
end_time
FROM
events
start_time)) * 12
age
FROM
weeks
events
FROM
FLOOR
age
AS
start_time
years
YEAR
start_time
SELECT
FROM
EXTRACT
events
age
start_time
start_time)) / 7
DAY
end_time
end_time
MONTH
months_difference
EXTRACT
SELECT
years_difference
end_time
AS
end_time
MONTH
FROM
FROM
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
.
|
Créer jeu