Formatting Dates For Reporting3:17 with Andrew Chalkley
The 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
You need to sign up for Treehouse in order to download course files.Sign up