Follow this step by step easy tutorial to learn how to use MySQL date and time functions and see how to modify the time formats.
Don't forget to check out our site http://howtech.tv/ for more free how-to videos!
http://youtube.com/ithowtovids - our feed
http://www.facebook.com/howtechtv - join us on facebook
https://plus.google.com/103440382717658277879 - our group in Google+
There are various functions which can be used for calculating the date and time in MySQL but for the purpose of this tutorial we will use 3 primary functions for date and time each.
For this tutorial, we will be using SQL YOG as an instance of MySQL.
Step 1- Current Date
Let's start with MySQL date functions.
For that write the following query:
and press F5 to execute. It will show the current date of the system. Curdate is a short form of current date.
Step 2-Changing date format
Now if we want to view the date in a specific format, we can use a function called the date_format().
Write "date_format" function in a similar pattern as before, but this time inside the parentheses, we will write the date within single quotation marks and then specify the required format in single quotations after a comma.
Here we are using W for week, M for Month and Y for years which are implicit format specified in the MySQL environment, and used with a percentage sign.
The actual query would be:
select date_format('2013-04-18','%W %M %Y');
Step 3- Viewing Name of the Day
In MySQL date functions library, there is a method which can return the day name, when given the date as its perimeter.
It is called "dayname" function. To use that, write dayname(), following the "select" keyword, and specify the date within single quotes inside the parenthesis.
The query would be:
Step 4- Time Functions
Now let's move on to mysql time functions
First of all let's find out the current time. For that, we will write curtime(),which stands for the current time, and write the query in the same way as we did for the current date.
Hence the query would be:
Step 5- Changing time format
Now let's see how we can change the format of the output.
For that, use the time_format() function, as we did for the date format.
Write the following query in the editor:
select time_format('14:28:18','%h : %i : %s');
Over here, h, i and s are used for hours, minutes and seconds respectively.
Step 6- Changing Time to Seconds
In MyQL time functions library, there are various functions related to time. One of them which is widely used by programmers is the time_to_sec() function.
This method changes the given time to seconds.
To use this, write time_to_sec() and specify the time inside the parentheses within single quotes.
In the Query Editor, write:
When we execute the query, we can see that the time being displayed is in seconds.
Step 7- Date along with Time
Lastly, to display the current date and time altogether, we can use now() function along with the select keyword.
in the query editor and execute it.
And this was all about MySQL date & time functions.