Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
MySQL provides a set of functions for dealing with dates and times. For this lesson we are not going to be selecting a database. We're not actually going to be using a database, we're just going to be using these functions directly like this. I can say Select Now and it gives me the current date and time. This machine is set to UTC which is universal time. Actually the sid script is set to UTC. So if you're using sid yours will also be. And especially, if you have installed the time zone support according to the instructions in the set up movies, then you'll have UTC.
If not you did not install the time zone support then what time zone you're in, it probably will not be UTC. It'll probably be your local time zone. So Now gives us the current time stamp, also Current Timestamp like this gives us the current time stamp. It's exactly the same. It's just an alias for Now. Now is the SQL standard. You can also get a Unix timestamp which looks like this. A Unix timestamp is an integer that represents the number of seconds since January 1st, 1970, at midnight at the beginning of the day, UTC.
You can get the name of the day like this, saying DAYNAME. And you need to give it a timestamp, so you can say NOW like that. And so, that, now, is inside the parentheses for day name. And, when I press Go, says it's Thursday. You can get the day of the month. Day of month, like that. You can get the day of the week. You can get the day of the year. And you can get the name of the month like this with MONTHNAME. There's also functions for converting, and adding, and subtracting time.
There is time to seconds, like this Time To Sec. And we give it a time like 0 hours, 30 minutes, and 0 seconds. And that will give us the number of seconds that represents, which is 1800. And if I type Sec To Time, and put in 1800, it'll give me 30 minutes back. Like that. You can also add and subtract time. You can say ADDTIME and give it a time in quotes like this.
Say 1 hour and another time, 29 minutes and 45 seconds. And it will add those together and give you 1 hour 29 minutes and 45 seconds. You can also subtract time like this say 1 hour and 30 minutes. And you want to subtract say 15 minutes like that and it gives you a result of 1 hour and 15 minutes. You can also add and subtract dates like this. Add date and put a date in quotes here.
And to add the date you want to give it an interval which is done with the interval keyword. Say 31 days and I use days singular, the manual says the day plural also works but in fact it does not. So you need to use day singular, the interval 31 day and press go. And you see it added 31 days, January 2nd becomes February 2nd which is 31 days later. You can also subtract dates like this. I say subdate that same interval it will give me December 2nd in the year before.
Integrals are available for hour, minute, second, day, week, month year and quarter. Micro second is also available on some platforms. My scale provides a good set of functions for handling dates and time. In the next lesson, I'll show you how MySQL handles time zones.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98223 Viewers
61 Video lessons · 85535 Viewers
71 Video lessons · 69488 Viewers
56 Video lessons · 101826 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.