Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Calculating dates are great for generating reports and dashboards that are dynamic in nature.
Documentation Links for Calculating Dates
Cheat Sheets
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up