Welcome back. In our last lesson we learned about many things you can do with strings. We're going to continue that discussion here by looking at a particular type of string dates. Date variables are tricky and can be difficult to work with, because they can be stored and formatted in many different ways. But dates are something that we use in analysis often. And as such, you're having to restructure this type of data frequently. We also use dates for different time series analysis and the time frame of the date you're looking at can be really important if you're doing any sort of clustering with your data. After this lesson, you should be able to: Describe the complexities of adjusting date and time strings, discuss some of the different formats in which dates and times are present, list and describe the five different functions in SQL that can be used to manipulate date and time strings. Let's begin. Working with dates is an important skill to have, but is also one of the more difficult things when it comes to working with SQL and databases. One of the things to note again is each data management system uses its own variety of data type. Again, you should probably look it up and see what are frequently used ones and which systems you're using. For this, I'm going to be specifically going over SQLite, since that's what we're using in this class. But again, just remember to pay attention to this in case you're using a different database management system for your job. One other things to remember here, is as long as your data contains only the date portion, your queries will work as expected. However, if there is a time portion involved it starts to get a lot more complicated. What I mean by this is if you look on the right hand side here, there's a lot of different formats. The first one we're spelling out the day of the week. We're spelling out the month and then we have the day and year. The other one we're abbreviating the month, day, and year and then we have a time and we also have the timezone it's in. There's even Julian format. There's a lot of different formats. If your date column simply has the date portion, such as 9/17/2008, things will work pretty easily. Things get complicated when we start to use those time stamps, which can contain hours, minutes, and seconds. Again, as we talked about, the most important thing is understanding how it's formatted before you start working with it. Let's go over that a bit more by looking at an example. If you query a date time and you say something like where the purchase date is 2016-12-12, you aren't going to get any results back and this is because it's in this format that also has the hours, minutes, and seconds. Sometimes you need to convert it, because maybe for your analysis, you don't maybe care about the level of detail. If you do, you can be very specific and say what you're looking for. But a lot of times we're just looking at things down to the day level. Knowing how to change this and manipulate it is important. To do this we have some different functions that we can use to manipulate. SQLite supports five different date and time functions, as displayed on the screen. So, you can pull out the data or something. If you're working with the time stamps, you can also just pull out the time. There's also the Julian day. There's a lot of different functions here that we can use depending on what we're hoping to do with dates. What you can see is that all of these kind of follow similar format. You start with declaring your function and then you do your timestring and modifier. The timestrings are what you want to extract from the date time function. As you can see here, what we have are a lot of different timestrings we can use. We can extract the day and the month, we can extract the seconds, hours, years or minutes. Really, any piece of information we're able to extract from that date time stamp. Then, what happens after we add in our time stamp, string, and modifiers? There's also a couple of modifiers that SQLite supports. To go into a little bit more detail into this, the timestring can be in a couple of different formats, as shown here. I'll provide some resources for you. There's a lot of great resources on the web and there's just one format that has to be in. Just pay attention to what you're looking for and then how you want to format it. And the same thing goes with the modifier. A timestring can be followed by zero modifiers or multiple modifiers. Each modifier transforms, that is applied to the time value is applied from left to right. The order is really important when you apply your modifiers and keep that in mind when you're doing this. Okay. So, that's an introduction to working with date and time formats in SQL. In our next lesson, we're going to continue this discussion by going over some more specific examples. This should help you understand how to use the functions we've discussed even better. So, I hope you're looking forward to that. I'll see you there.
