Simple Date and Time Functions in MySQL

Date and Time in MySQL - Part 2

Foreword: In this part of the series, I talk about simple date and time functions supplied by MySQL.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

This is part 2 of my series, Date and Time in MySQL. In this part of the series, I talk about simple date and time functions supplied by MySQL. Such functions make your life easy and you do not have to write functions to handle dates and times. There are many parts to this series and you have to read the parts in the order given.

Function
A function is a peace of code that performs a particular task. What we shall be dealing with is a function call. A function call is the name of the function followed by parentheses. When a function call is typed, it calls the complete function unseen by you to carry out the task. What goes into the parentheses of the function call is called argument (or expression).

Use of Date and Time Functions
You will normally use date and/or time functions in SQL statements especially the SELECT statement. Assume that you have a table of animal pet names and their dates of birth. The following SELECT statement will return the names of the pets whose dates of birth is less than the current date (today’s date).

SELECT name FROM PetsTable WHERE birthDate < CURDATE();

Here the function (call) used is CURDATE(). At the position of the function call in the SQL statement you will effectively have the current date value. The function will do the replacement, leading to a classical SQL statement such as:

SELECT name FROM PetsTable WHERE birthDate < '2014-09-15';

So, in a SQL statement, you place a function (call) where you want a value, which will replace the function call (code) as a result of the work of the function. In the above (first) SQL statement, names of pets delivered at the current date (today) will not be returned (because of the use of < and not <=).

You now know why date and time functions are important. You also know where to write them in a SQL statement. I will spend the rest of the series describing individual date and time functions.

Simple Date Functions

The Date() Function
The date function extracts and returns the date part of a datetime expression. The syntax is:

    DATE(expr)

You can use lower or upper casing for function names. The following statement illustrates the use of the date function:

    SELECT date('2004-12-31 01:32:03');

The result displayed is, 2004-12-31. Note that this SELECT statement has not used any database table. In MySQL the SELECT statement can operate on expressions that do not have a database (table). Well, let us look at a case now that would use a database table. Assume that you are using a database table called, myTable and there is a column in the table called actionTime with a data type datetime. Assume that in the third row of the table with a primary key ID of 3, the value of the column, actionTime is '2004-12-31 01:32:03', then the following SELECT statement will extract 2004-12-31 from the value:

    SELECT date(actionTime) FROM myTable WHERE ID = 3;

Here ID is the name of the primary key column. Note: the reserved words such as SELECT, FROM and WHERE of the SQL statement, can be in lower case. Note that the name of the column having the datetime value is the argument value of the date() function.

You should now know how to use a MySQL date and time function for a table in a database. For the rest of the examples in this series, I will use the SELECT statement without a database (without a table).

The CURDATE() Function
This function returns the current date in the 'YYYY-MM-DD' format, everything being equal. The syntax is:

    CURDATE()

You can try the following code that works:

    SELECT curdate();

In the return value, you will not see the quotes, but it is understood that the date returned is a string. The date is the date of the computer read by the MySQL server.

The UTC_DATE() Function
UTC time (date) is GMT time, which is the time in Britain. From the east to the west in the world, time (date) is different in different countries at the same moment. UTC time is the reference time. This function returns the current UTC date, which may differ from the date in the computer that has MySQL server. It is the computer date that the MySQL server translates to the UTC equivalent. The syntax to use the UTC_DATE() Function
is:

    UTC_DATE()

You can try the following statement that works:

    SELECT utc_date();

The MAKEDATE Function
This function takes a comma separated argument list of year, and day-of-year and returns a date string object, which includes the month and day of month. There are more than 300 days in a year, so the day of year is different from the day of month. The syntax is:

    MAKEDATE(year,dayofyear)

Try the following code that works:

    SELECT makedate(2012,46);

In this case the result is 2012-02-15. If the year had been given in 2 digits in the argument list, a four-digit year would still have been returned. However, I do not advise you to give the year in 2 digits.

The Time Functions

The TIME() Function
This function extracts the time part of the datetime expression and returns it. The syntax is:

    TIME(expr)

The following statement illustrates this:

    SELECT time('2004-12-31 01:32:03');

The return string is, 01:32:03.

The CURTIME() Function
This function returns the current time in the format, 'HH:MM:SS'. The syntax is:

    CURTIME()

You can try the following statement that works:

    SELECT curtime();

The UTC_TIME() Time
I explained the meaning of UTC above. UTC time is the time at the moment of interest in any part of the world. This is a reference time and it is the time in Britain, no matter where you are in the world. This time is read from the computer by MySQL server and translated to the time in Britain at that moment. The syntax is:

    UTC_TIME()

The following statement illustrates its use:

    SELECT utc_time();

Do not confuse between time and date (UTC time and UTC date).

The MAKETIME Function
This function takes a comma separated argument list of hour, minute and second and returns a time string object. The syntax is:

    MAKETIME(hour,minute,second)

Try the following code that works:

    SELECT maketime(5,3,45);

The result should be '05:03:45' .

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Simple Date and Time Functions in MySQL

Date and Time in MySQL - Part 2

Introduction

Simple Date Functions

The Time Functions

Related Links

Comments