Preview
Video Player
00:00
00:00
Calculating Dates
4:03Calculating dates are great for generating reports and dashboards that are dynamic in nature.
Imagine you had to create a dashboard for
0:00
executives of the e-commerce
site to look at.
0:02
They'd want to see things like the number
of sales in the last seven days and
0:05
the last 30 days.
0:08
How do we go about doing that?
0:10
You've already seen the DATE
function using the string of now.
0:13
This is known as a time string.
0:17
We'll look at some more
time strings later.
0:19
The DATE function can take another
argument called a modifier.
0:22
A modifier modifies or changes the time
string value passed as the first argument.
0:26
In fact, you can chain any
number of modifiers together.
0:33
Let's look at some examples.
0:37
You've seen the time stream of now,
but you can give it specific dates.
0:39
If you wanted to write
the first of February 2016,
0:45
you'd include the year first,
then the month, and then the day.
0:47
Any other way won't be recognized.
0:53
This format is recognized by the SQL
programming language and can be modified.
0:56
If you want to get the date from one week
ago we can use a modifier like this.
1:01
This would return the date
of the 25th of January 2016.
1:06
You can do any addition and
subtraction here.
1:09
We can go forward seven days.
1:14
There are other modifiers too.
1:16
[SOUND] You can do the same for
months and for years too.
1:19
Let's take a look at combining
some of these modifiers together.
1:23
Let's find out if February
2016 has an extra day.
1:27
First, let's go forward a month.
1:30
[SOUND] Then we can go back one day.
1:32
[SOUND] This tells us 2016
is indeed a leap year.
1:35
Let's answer some questions that would be
appropriate to help create a dashboard for
1:41
an e-commerce site's stake holders.
1:46
The first question is, how many orders
happened in the last seven days?
1:49
Our query starts like this.
1:54
Select count from orders.
1:56
We want to add a WHERE condition where
1:59
the ordered_on is between two days.
2:04
What's the first date?
2:17
Now minus seven days.
2:20
Now what's the last value
of the between clause?
2:27
It's ("now", "-1 day").
2:31
You may be asking why not today?
2:38
Because today is still happening, and
today would be part of the eighth day.
2:41
So in order to get the sales from
the last seven days we can use,
2:47
seven days ago and yesterday.
2:51
Let's answer another question.
2:56
How many orders happened during
this seven day period 14 days ago?
2:57
Basically, were the sales
from a forthnight ago more or
3:02
less than the weeks just passed?
3:06
We can modify our statement from
before and minus an extra seven days.
3:08
But this is equivalent of minus
14 days and minus eight days.
3:21
Breaking out like this may
help you understand what's
3:27
going on better than seeing
the numbers 14 and eight.
3:32
You can start to see, we can use
SQL to get values to present on web
3:36
dashboards, or even use in a spreadsheet
to generate charts of historical data and
3:41
makes decisions based off it.
3:46
At Treehouse we use Mode Analytics to
generate reports and create dashboards for
3:49
people to look at.
3:54
It helps us achieve company-wide goals and
3:55
it helps us modify our behavior
in response to the data.
3:58