Preview
Video Player
00:00
00:00
Formatting Dates For Reporting
3:17The dates stored in a database often don't suit a human reader. In this video we'll update the dates to be more friendly!
We've calculated dates to help
us with writing queries, but
0:00
what about the presentation of results?
0:03
To understand how we can manipulate
the output of dates and times,
0:06
let's talk about the different date types
that you may come across in a database.
0:10
There are three types of date datatypes.
0:16
Remember, datatypes prescribe the way
data in a column should be stored.
0:19
The first two are date and time.
0:24
For dates, you'd stored dates like this,
[SOUND] then time looks like this.
0:27
Then this datetime where date and
time are combined in a single column.
0:32
Datetimes must be written like this, but
this can be hard for people to read.
0:37
Most programming languages provide
functions to convert the types of dates,
0:43
computers and database uses into
a more human readable format.
0:47
Let's take this time string for example.
0:51
If we didn't care about the time parts,
0:55
we could use the Date
function to trim it off.
0:57
You may use the Date function
in the condition where you
1:00
are only concerned with the day and
not the time.
1:04
If you only wanted the time,
you can use the Time function.
1:07
This may be good for just showing the
times of appointments on a given day or
1:11
the times of TV shows.
1:16
But with both of these functions
they're still not very quick for
1:18
us humans to read.
1:21
This is where this function comes in.
1:24
This function,
which I've never said out loud before, but
1:26
I'm gonna call it STRFTIME or string
format time, is very similar to date and
1:30
time, except it has an addition of a new
first argument, the format string.
1:35
Let's say we wanted to generate
the string of 01/04/2015.
1:40
Which is the British way of writing dates.
1:46
We include the format string of %d/%m/%Y.
1:49
The %d means day, %m means month,
and the %Y means year.
1:57
You can put anything else
around these substitutions.
2:04
In this case, I've used slashes to
separate each part of the date.
2:08
You can even use modifiers too.
2:13
Such as adding or subtracting days,
months and years.
2:16
Let's say our e-commerce
store was in the UK.
2:21
Let's bring back the orders
with the UK date format.
2:25
Let's start with this query.
2:29
We have all columns and
then we have the ordered_on AS UK_date.
2:31
Let's use the STRFTIME function to
manipulate the ordered_on column
2:37
Remember the first argument
is a format string.
2:45
When you run this now,
we get all columns plus
2:51
an additional set of values,
the UK_date for each row.
2:56
Awesome.
3:02
I've included examples in the teacher's
notes of all of the possible substitutions
3:04
that you can include in the format string.
3:09
I've also included links to
the documentation sites for
3:11
other common SQL implementations.
3:14