Calculating Dates4:03 with Andrew Chalkley
Calculating 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
You need to sign up for Treehouse in order to download course files.Sign up