Join Sheeri Cabral for an in-depth discussion in this video Date and number functions, part of Up and Running with MySQL Development.
If we want to do date based arithmetic, there are functions for that. For example, we can subtract dates with a DATEDIFF function. Let's see how many days there are between Tony and Cherry's birth day. DATEDIFF takes two arguments, the dates just subtract. Just like with regular subtraction, you put the larger value first. So the more recent value is first. Which is 1978-09-17, and then then the less recent value, 1975-01-27.
So Tony is 1,329 days older than Cherry. What if we wanted that in a more friendly format? We can use the FROM_DAYS function to convert days into a year month day format. So if we say SELECT FROM_DAYS(1329) We get that Tony is three years, eight months, and 22 days older than Cherry. You can change functions so we can go back to the first DATEDIFF query and wrap the FROM_DAYS function around it.
Now we get that all in one query. If you want to find out what the current date or time is, there are a few functions to know about. These take no arguments and are called current date for the current date, and current time for the current time. You can also get the UTC timestamp The current time stamp, and an alias for the current time stamp called NOW, they're the same thing, and then a similar function called SYSDATE. So you can see that the UTC time stamp, is in UTC time, where the current time stamp now in sysdate, all have the same values.
SYSDATE gets the system's time when the query executes is different from the Now function in a subtle way. In order to show you this difference, there's a neat function called Sleep, which takes a number or fractional number of seconds. The goal is an approximate amount of time where the query is not doing anything. So let's take a look at values of SYSDATE before and after a SLEEP. For example, SELECT SYSDATE NOW and then SLEEP for 1.5 seconds and then, again, show us SYSDATE and NOW.
And let's do it in a vertical format. So you can see, at the bottom it says one row in set and it took 1.5 seconds. The value of SYSDATE and NOW are the same value before the SLEEP but after the SLEEP, SYSDATE is later then NOW. That's because NOW is taken once at the beginning of a query and will be the same throughout the query SYSDATE is evaluated separately each time it comes up. we can also do some simple math with my SQL. For example select three plus two as sum.
Three minus two as diff. Three times two as multiply. three divided by two as divide. We can also do a little more complicated math. For example, select three to the power of two. Three squared which would be nine. The square root of say, 100 and a modulo say, three modulo two and you can see that we've got nine as three squared and square root of 100 and one is the modulo of three, modulo of two.
There are more complex functions you might be is to having for example, absolute value Ceiling and floor not worked as just as you would expect them to. There are also other functions for example, log base 10, so log base 10 of a 1000 and log base two, so we can see that those are the correct answers.
We also have even more complicated mathematics, for example, if you wanted to get E to the power of something, use the EXP function. You can also use PI, so let's say we wanted to have PI R squared as the area of a circle, where our radius is three. So we want PI times three squared, So let's see if we can get that done. And that, in fact, is what we have. There are also things like Sin, Cosin, Tangent, so we can do that if we really want.
Cosin of one, all that kind of thing, Tangent, Cotangent. So there are plenty of mathematical functions to keep you busy. Now, you know ways to manipulate dates and times. You know about the sleep function and the difference routine now and SYSDATE. You've see how to do simple and more complex mathematical functions.
- Exploring MySQL data types
- Creating a database
- Creating tables
- Reading and adding data
- Using date and number functions
- Sorting results
- Inserting and replacing records
- Joining tables