PL-300 – Section 17: Part 2 Level 5 – Transform – Dates and Time Part 2

137. Transform/Add Column – Dates in Other Cultures/Languages

Now, let’s say that you have got year, month and day in separate columns and you want to combine them together and then make a date. How can you do that? Well, you could use the merge columns feature, highlight the columns, and then merge columns.

Now, I’m just going to put a space separator, just for the moment and you can see the result. 2022 one two, not really the order that we want.

Now, we can change it in the formulas, obviously we can change where they are in this table, but we can change it in the formulas. Suppose, we wanted the columns to stay in this same place. So, here we have the year text dot from converts into string. So converting the year, converting the month, converting the day. So, what I want is it in American format. So American format is month, day, year. So, I’ll put the year right at the end, and we’re going to separate it, not by the space that we’ve done previously, but by slash. And now we have got something that looks like a date, albeit it is in text format, we can convert it into a date, like that, or we can go to parse. So, I will transform this data using parse, until it reads the date, and so it becomes, it tries to interpret it as a date.

Now, let’s just have a look at that formula again. Because there’s something interesting going on here. EN hyphen US. English, United States. That is called the culture, and that is going to be useful for this particular video. So, it’s a dot net culture specifically, and if you look up as I’ve done for instance, net culture FR hyphen FR which is the French of France, one of the hyperlinks you get is this, which gives you a full list of various cultures. So EN hyphen US, you can see is United States. So how can this help with interpreting dates from a different culture? Well, let’s remove this parsed date, and let’s instead of having this being in the US culture, month, day, year, we’re going to change the order so that the dates that we’ve got are in the British order.

So, here we have the British order which is day, month, year. And now we’re going to parse this. And you can see we’ve got problems. The computer is parsing it thinking this is American because this computer has been set up in an American locale. So, for the first row, the computer thinks that’s going to be the first month, first day, 2022. That’s February, that’s March, but it’s not, it’s in the British format. And when it gets to the twelfth, okay that’s December the first, it thinks, not the twelfth of January, and then when it gets to the thirteenth, it hasn’t a clue and so it just gives an error. So how can we use this parse to say okay, these dates are from Britain. Well, what we can do is use the culture that we were just talking about. So, the culture name for Great Britain is EN hyphen, and I think it’s GB. So look it could be UK, not, there we are, so it’s English, United Kingdom EN GB. But the question is why are you able to put it into this formula. There isn’t a EN hyphen US already. These dot from functions have got two arguments, and the second one is culture as nullable text. So as nullable text means it doesn’t have to be there. So, what we’re going to do this, let’s work this out. The underscore is what’s already there. So give me that format cell, convert it into a time, date time zone, we’ll be getting into date time zone later, but just for now know it’s a date time which also has a time zone. And then it converts it into a date, so it gets rid of any time. So, it’s this first one that’s going wrong. It’s getting your text; it’s converting it into date time zone. Wrongly. So, this has a culture as a nullable text, so right here, that is the value that we’ve got at the moment, use the value sorter there. So, I’m going to put a comma, and then in quotation marks EN hyphen GB and press enter.

And now you can see it works. The computer has interpreted it correctly, the errors have disappeared, and because this is a date, and because my computer is set up in American, in United States, then it is being displayed in the American format. So that’s how you can get from a British date into an American date. And similarly that can also happen the other way around. So, I’m going to have a small modification of what we’ve already got. Suppose we had this date, but we wanted the month name, not in English, but in French.

Okay, so, let’s go back to where we’re inserting the date, the month name. And you can see the formula is date dot month name, but when I hover over it you see again there is a culture nullable text. So, I can change or add in a second argument. So, I’m going to put the French of France. FR lower case hyphen FR upper case. And now instead of January with a capital j, we now got janvier.

Similarly, I could in Spanish, EN, sorry ES hyphen ES. So enero, febrero and so forth. So, I can do the same with the date name inside the day name. So, again after day, because if I hover over the of day of week you can see culture. I can change that again ES hyphen ES. So now we got sabado, domingo, lunes, martes, miércoles and that sort of dates. So, suppose, instead of having one one 2002, so month, day 2002 or the year, instead of that I did have the month I had the month name. And I’m going to use spaces to separate them. So, I’ve got one enero 2002. So, I can try parsing that, and you can see I can also convert it to date time, but that will just get me an instant error. So, if I parse that I still get the same error but now I can modify the culture. So that I can set the computer exactly Spanish of Spain, press enter and there we get the interpretation of one enero, two enero, one febrero marzo and all the rest into American style dates or, however, the computer is set up on your computer, however, the format is.

So culture, very important if you have got data that is coming from a different culture to the one that your computer is set up. Similarly, you can export into a different culture. So, I could have a column for English names, French names, Spanish names and so forth, if I so wished. And in this video we’ve had to look also at the formulas that have been used and seeing that there is a hidden second argument that we can put whatever culture you are currently using, or wish to use to interpret the data that you got.

138. Transform/Add Column – Time

In this video, we’re going to have look at the time functions that we’ve got.

So, we’re going to create again, a new query starting from a blank query. And we’re going to have a list of dates, but instead of using dates or times, I’m going to use DateTimes which combines dates and times. So instead of starting with a date, I’m going to start with a DateTime. So, the difference between hash or pound date and datetime, is that date requires three functions three arguments, year, month, day, whereas datetime requires six. So, we have the year, the month, the day, the hour, the minute, the second. So, I’m going to be starting at midnight on January the 1st 2022.

Now, we’ve List.DateTimes, you then say what the count is, how many individual times do you want, I want 9,000 and the step this is as a duration So again, pound equals #duration.
Now, duration has four arguments, days, hours, minutes, seconds. If you’re wondering, by the way, why say months isn’t part of the duration, then it would be impossible for the computer to do it. Because how would you tell the computer what a month is? Is it 28, 29, 30, or 31 days. There are ways that we will get into to advance dates and times and we’ll handle that in the next video I think. So, if you want to go a day later, you can do that or a month later. So, here we now have a list of 9,000 rows, which have got various days and times just 15 seconds away from each other. So 15 minutes away. I’m also just going to put in a little randomness just for the purposes of this video, I wouldn’t do this otherwise, just to change the seconds. And I’m going to convert this into a table. So, this is my date time. And I’m going to just put the words column just in case, it gets confusing, and I’m going to call this a date time datatype.

So, let’s have a look at the transform and the add for times. Well, first of all, you can add the time only. So, this will separate out the date from the time. So, similarly, with the dates, you could have the date only. So that gives me just the date. Then we can have the number of hours. So, here we have the hour of that particular time. And then the minutes and the seconds.

Now, you can also get what the start of the hour and the end of the hour is. So, if I just simplify this by just removing a few columns, I can look at what the start of the hour is. So instead of it being one and four seconds, it’s one o’clock, 1:15 and five seconds, it’s one o’clock. And similarly the end of the hour, it looks like it’s the next hour, but if you click onto it you can see it’s the next hour minus that fraction of a second, not quite .0000001 of a second.

Now, what’s the difference between these two? Well, we can highlight that and go to Subtract. So, you can see there’s 15 minutes and one second. Notice that the difference between two times becomes a duration. In the next video we will look at all of the duration functions.

Now, just to let you know, that again, you can combine date and times. So, if I extract an additional column, the Date Only and then the Time Only, We can highlight these two columns and Combine Date and Time. So, you could have that into a new column or you could transform your existing columns and have it in a new, single existing column. And likewise as for dates, you can have the earliest and the latest. And you can parse dates as well. Again there may be a cultural impact on this. And there is not as much cultural impact as you might imagine for dates. For dates you could have January (in foreign language) lots of different languages. It’s a bit harder to put times into various cultures. But there are still some cultural differences. For example, the time 5:25 in Japanese, might be expressed like this, with a little character in between for the hours and the minutes. And so that could need some representation. Equally, if I was to go into say Spanish, and say “what is 25 past five?” And start talking about AM or PM, then Spanish speakers don’t use AM or PM. So, there may be some cultural requirements with parsing that, but probably a lot less than you’ve got with dates.

However, just a quick demonstration of how to parse. So, I’m just going to get the hours, the minutes, the seconds, going to combine them all, with a colon in between. So, this could be the source data that you’ve got. And then you could parse it like that.

So, this is how we work through the time functions, very similar to the date functions. In the next video we’ll have a look at the difference between times and dates being durations. So like this being a duration. And the functions that we have got for them.

139. Transform/Add Column – Duration

Now, in this video, we’re going to look at duration. And it’s the same setup as we’ve had previously, I’ve just removed a few columns. So, duration is the gap between two date times or two dates or two times. So, it is measured with four attributes: day, hour, minute, second. And you can extract those at the top so you can have days, hours, minutes, or seconds. So, if I was to extract seconds, for instance, you’ll see that it has extracted the thing right at the right and side. So fifteen minutes and one second, it’s extracted the one. So, this isn’t extracting how many seconds 15 minutes and one second is. It’s just extracting one second.

Now, you can do that by using total years, total days, total hours, total minutes, total seconds. So, if I click on total seconds, you can see that 15 minutes and one second is 901 total seconds. So, you may remember in a few videos previous, I said you could have the difference between two dates and then we can change this so we can see the difference in hours or the difference in minutes or the difference in seconds. Or we can also adapt that to say the difference in total seconds which gives us an identical formula to what we’ve got here. It’s just another way of getting to it. A lot of these drop downs give very similar results. So, I’ve changed subtract days to subtract total seconds, very easily.

Now, if I was to get a second duration, I can subtract the two away from each other. So, obviously, these two have been curated using exactly the same process and so the difference is zero but you can see that duration is able to subtract one value from another.

We can also multiply and divide durations. For example, we have this duration. What happens if it was doubled? So, I can multiply by two or I can divide by two if you want to know what it’s like halved. And, of course, you can edit the formula if you so wish to refer to another column.

And then there’s all the standard things that you can do with durations. Like you can do with time and date, you can have the sum, the min, the max, the median and the average altered in a way in the statistics. But very rare that you actually need to use them. Most of what we’ve got here for duration is Duration.Days, hours, minutes, seconds and total days, total hours, total minutes and total seconds. You’ll have seen, by the way, years. That is simply total days divided by 365.

So, durations, one date time, or date, or time minute, another can be manipulated.

• Category: Uncategorized