Table of Contents
SQL Time Functions - Overview
In this article, you are going to learn about SQL Time Functions. Each SQL Time Function will be explained with practical and real-life examples. Like SQL Date Functions, Time functions can have different versions in different Database Management Systems like MySQL, ORACLE, and MS SQL Server. SQL Date functions that expect date values usually accept DateTime values and ignore the time part. Hence we need to learn different types and versions of SQL Time Functions which expect time values usually accept DateTime values and ignore the date part.
SQL Date or Time Functions are evaluated at the start of query execution and return the current date or time. This is the generic principle of use of SQL Time functions that applies to all functions. For example, If we use NOW() functions three times in a SELECT Statement, at each SQL time function instance the same value will be returned. This principle also applies to
UTC_TIMESTAMP(), and to any of their synonyms.
Additionally, it is to be learned that
FROM_UNIXTIME() SQL time and date functions return values in the current session time zone.
Different SQL Time Data Types
Before we start, let's recap about the Time data types that are used to store Time or DateTime in SQL. SQL DateTime data type is the primitive data type that can also store the date as well as the timestamp in the database. But there are some specific SQL Time Data Types that are used to store only the time part. These Time data types are different in different Database Management Systems like SQL Server, MySQL, MS Access, etc.
Here is the list of SQL Time data types that are widely used by most database management systems:
- The TIME data type can only store the time in the format
- The DATETIME data type can store both the date and the time information in the format
- The TIMESTAMP data type can store the date and the time between a range of '
1970-01-01 00:00:01' UTC to '
2038-01-19 03:14:07' UTC.
- SmallDateTime(SQL Server) data type can store only the date in
- DateTime2 data type can store the date and the time in
YYYY-MM-DD hh:mm:ss[.nnnnnnn]format. It can store nanoseconds.
- DateTimeoffset (SQL Server)
Working with SQL Time Functions
Working with SQL Date and time is the difficult part for the database designer and programmer. Different time zones, different time formats, and complex time calculations make it more critical and logical for them. As far as working with SQL Dates is concerned, is easy to handle with the help of various SQL Date Functions. But it becomes more complicated if the time portion is also stored in the database with the date.
This article will describe the working with SQL time functions and will make it easy for you to learn about each SWL Time Function. We will use real-life practical examples and code snippets with sample output for an explanation of every SQL Time Function. In these examples, different scenarios will be demonstrated in which one needs while writing SELECT queries including the calculations and manipulations of the time.
Here is the list of the most commonly used SQL Time functions that are used to manipulate the time stored in the database as per the requirement of the SELECT Query. We will try to explain one by one SQL time Function with relevant examples and the SELECT statement example code.
1. NOW( ), CURTIME, SQL TIME Function with its Synonyms
CURRENT_TIMESTAMP() SQL Time functions are interchangeable and synonyms of watch other that returns the current date and current time of the system. There are also many variations of these functions are available in MS SQL Server and MySQL and all other Transact SQL type languages. Here is the list of all SQL Time functions that return both the date and the time or only the time
NOW()return both the date and time
GETDATE()return both the date and time
SYSDATETIME()return both the date and time
LOCALTIMESTAMP()works same and return the time. The format of the returned time may be different. Most of these SQL time Functions are the same in MS SQL Server and MySQL but few of these functions have different synonyms names.
LOCALTIMESTAMP(), all these SQL Time Functions return the current time in different format.
It is programmer's need and practice to use any of these. Let's see the sample output of the different SQL Time Functions of MySQL after execution of the following SELECT Query.
SELECT NOW(), curtime(),current_time(), current_timestamp(), current_time, localtime(),localtimestamp();
2. GETDATE(), SYSDATETIME() Time Functions with Synonyms in MS SQL Server
In MS SQL Server, there are a lot of SQL time functions like
GETUTCDATE() which can be used to return the date and the time.
No single SQL Time function is dedicated to returning the time only in the MS SQL Server. We will have to either use the
DATEPART() function to get the time from the returned
DateTime value in the above SQL Date and Time Functions.
Let's see the sample output of the different SQL Time Functions of MS SQL after execution of the following SELECT Query.
SELECT SYSDATETIME() as SYSDATETIME ,SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET ,SYSUTCDATETIME() as SYSUTCDATETIME ,CURRENT_TIMESTAMP as CURRENTTIMESTAMP ,GETDATE() as GETDATE ,GETUTCDATE() as GETUTCDATE ;
Use of Convert() with SQL Time Functions in MS SQL Server
In above Figure2, showing the sample output of the use of SQL Time Functions in MS SQL Server. It can be seen that no SQL Time Function returns the time only. All versions of
SYSDATETIME() and its synonyms functions return both the date and the time with offset zone. Hence the problem here is that SQL Server has several different time functions showing different both the date and the time.
Here is how we can get the time from Timestamp or DateTime value returned by all SQL Time Functions in MS SQL Server. Use of
CONVERT() SQL Functions with these functions can make it easy for the programmer to extract the time only from all the above values shown in Figure 2.
Let's see the sample output of this SELECT Query in which have used
CONVERT() SQL Function with above used SQL Time Functions. You can observe the change in this query that every SQL Time Function is passed as a parameter to
CONVERT() Function with TIME parameter. the TIME Parameter with extracts the time only from all Timestamp or DateTime values returned by all these SQL Time Functions.
SELECT CONVERT(TIME, SYSDATETIME()) as SYSDATETIME ,CONVERT(TIME,SYSDATETIMEOFFSET()) as SYSDATETIMEOFFSET ,CONVERT(TIME,SYSUTCDATETIME()) as SYSUTCDATETIME ,CONVERT(TIME,CURRENT_TIMESTAMP) as CURRENTTIMESTAMP ,CONVERT(TIME,GETDATE()) as GETDATE ,CONVERT(TIME,GETUTCDATE()) as GETUTCDATE ;
3. HOUR(), MINUTE() and SECOND() Time Functions in MySQL and SQL
Sometimes you may need to get the Hours, Minutes, Seconds, or Microseconds from the given DateTime Value. These four basic and primitive SQL Time Functions can return the required output. These SQL Time functions are very common in use.
- The SQL
Hour()function returns the hour part of the DateTime value.
- The MySQL
Minute()Function is used to return the minute part from the given DateTime value. These values can be between 0 and 59.
- Similarly, the
Second()SQL time Function will return the second part.
Suppose you want to get the hour, minutes, and the seconds part separately in number format from the DateTime value. Here is the code example which will guide you on how to get hours, minutes, seconds, and microseconds from DateTime in MySQL. Let's see the syntax of these functions first.
Syntax of HOUR(), MINUTE(), SECOND() and MICROSECONDS() Functions
HOUR(datetime) MINUTE(datetime) SECOND(datetime) MICROSECOND(datetime)
All of these functions take only one argument that of the DateTime type. Here is the SELECT Query which is used to find the Current System DateTime Value and to get the Hours, Minutes, Seconds, and Microseconds respectively from the current system DateTime value.
SELECT NOW() AS DATETIMEVALUE, HOUR(NOW()) as HOURS, MINUTE(NOW()) AS MINUTES, SECOND(NOW()) AS SECONDS, MICROSECOND(NOW())AS MICROSECONDS;
4. How to get Time, Hour, Minute, Second, and Millisecond in SQL Server
As you just saw in Section 3 that MySQL has dedicated SQL Time functions that can get Time, Hour, Minute, Second, and Millisecond Part from a DateTime value. But this is totally different in MS SQL Server as there are no such functions exist. However, many times you may need to get the Time, Hour, Minute, Second, and Millisecond Part from DateTime in MS SQL Server.
DATEPART() SQL function is used to get the HOUR part of the DateTime value in SQL Server.
You have already used DATEPART() in SQL DATE Functions.
Use of DATEPART() SQL Time Function in SQL SERVER
The syntax of
DATEPART() functions was explained with practical examples in our SQL DATE Functions Article. You will have to specify only the parameter, hour, minute, second, and microsecond to get these values from a DateTime value.
Let's try to write the same SELECT Statement in MS SQL Server to get the hour, minute, second, and microsecond values respectively from the current DateTime Value of the system. There are two changes in this query, SYSDATETIME() SQL Time function is used to get the current system date and time.
In section 2 of this article, you may see the list of all its synonyms also.
SELECT SYSDATETIME() as DateTimeValue , DATEPART(hour, SYSDATETIME()) AS Hours, DATEPART(minute, SYSDATETIME()) AS Minutes, DATEPART(second, SYSDATETIME()) AS Seconds, DATEPART(milisecond, SYSDATETIME()) AS MicroSeconds;
Note: It is to remind you that the DATEPART() parameter can have mm for minutes, hh for hours, ss or s for seconds, and ms for milliseconds.
5. ADDTIME(), TIMEDIFF() Time Functions in MySQL
These are two different functions that are used to perform addition and subtraction in DateTime value in MySQL. You have already worked on
DATEDIFF() functions which were for Date Value. Similarly, ADDTIME() MySQL Function is used to add an interval of hours, minutes, seconds, or milliseconds to the Time.
TIMEDIFF() Function is used to subtract an interval to the time. Both Functions have the same syntax and the same number of parameters to work on.
Syntax of the ADDTIME() and TIMEDIFF()
ADDTIME(datetime, addtime) TIMEDIFF(datetime, addtime)
Let's see the example code here to understand the use of how to add and subtract time from a DateTime value in MySQL. Suppose we have got the current time of the system and want to add hours, minutes, seconds or milliseconds in this time.
SELECT CURRENT_TIMESTAMP(6) as CurrentTime, ADDTIME(CURRENT_TIMESTAMP(6), "0.000007") as AddedMicroSec, CURRENT_TIME as CURRENTTIME, ADDTIME(CURRENT_TIME, "10") as AddedSec, ADDTIME(CURRENT_TIME, "0:10:0") as AddedMin, ADDTIME(CURRENT_TIME, "10:0:0") as AddedHours;
In this SELECT Statement following key things you must note and learn:
- The use of CURRENT_TIMESTAMP(6) SQL Time function to mention that we need milliseconds with 6 decimal precision. These values can be changes if you want to show the value of milliseconds with less or higher precision.
- CURRENT TIME only returns the time-value with hh:mm:ss Time Format.
- If you want to add the seconds only you will have to pass the parameter in the same format hh:mm:ss as 0:0:10 which means the no addition in hh and mm. The hh value can be between 0-23, mm and ss value can be between 0-59.
- If you want to add the minutes only you will have to pass the parameter in this way 0:10:0 which means the no addition in hh and ss.
- If you want to add the hours only you will have to pass the parameter in this way 10:0:0 which means the no addition in mm and ss.
You can observe the output that we have added 10 seconds, 10 minutes, and 10 hours in the current time of the system. You can add hours, minutes, seconds, and milliseconds at the same time in the time value. Here is the sample code and sample output.
This SELECT query will add 5 hours, 10 minutes, 15 seconds, and 6 microseconds in the returned system time by CURRENT_TIMESTAMP(6) with 6 digit precision.
SELECT CURRENT_TIMESTAMP(6) as CurrentTime, ADDTIME(CURRENT_TIMESTAMP(6), "5:10:15.000006") as AddedHourMinSecMicoSec;
TIMEDIFF() functions can be used in the same way as ADDTIME() is working in the above examples and will subtract the time despite addition. No change in syntax and the parameters. Just you will have to change the name of the Function in the above ADDTIME() Examples like this.
SELECT CURRENT_TIME as CURRENTTIME, TIMEDIFF(CURRENT_TIME, "10") as SubtSec, TIMEDIFF(CURRENT_TIME, "0:10:0") as SubtMin, TIMEDIFF(CURRENT_TIME, "7:0:0") as SubtHours, TIMEDIFF(CURRENT_TIME, "6:07:06") as SubtHoursMinsSecs;
In this SELECT Statement, you can observe the change in the use of Function. TIMEDIFF() function is being rather ADDTIME() and the other part of the statement is almost the same as in previous ADDTIME() Examples.
In the output you can observe that the current time value is returned by the system with
CURRENT_TIME Function. in
SubtSec Columns only 10 seconds are subtracted as mentioned in the query. Similarly, in
SubHours column, only 10 minutes and 10 hours are subtracted respectively. In the last Column
SubtHoursMinsSecs, you can see the 6 hours, 7 minutes, and 6 seconds are subtracted from the CURRENT Time value.
6. How to add/subtract Time in SQL Server | DATEADD() SQL DateTime Function
In MS SQL Server, you will not find the dedicated
TIEMDIFF() SQL Time Functions to add or subtract in the time value. While Time manipulation is commonly required in database scenarios when retrieving or storing date and time Microsoft SQL Server database. We can use
DATEADD() SQL Time Function in conjunction with DATEPART arguments_list for addition and subtraction in the time value.
Here is how you will learn how to use the
DATEADD() SQL DateTime function in SQL queries for adding or subtracting DateTime values.
DATEADD(datepart, Units, Input_DateTime)
Here interval means the in which part of the DateTime you want to add or subtract. Here is the list of SQL Time functions related list of arguments from the whole is of datepart arguments.
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
- microsecond, mcs = Microsecond
- nanosecond, ns = Nanosecond
Let's see the code below for SELECT Statement in which you will see how to add the time-unit in DateTime value returned by the GETDATE() Function. In this SELECT Statement, we will use DECLARE Statement to declare a local variable @Date to store the DateTime value return by the GETDATE() SQL DateTime Function.
DECLARE @Date datetime2 = GETDATE() SELECT @Date, DATEADD(MILLISECOND,1500,@Date) AS AddedMiliSecond, DATEADD(SECOND,12,@Date) AS AddedSeconds, DATEADD(MINUTE,10,@Date) AS AddedMinutes, DATEADD(HOUR,3,@Date) AS AddedHours;
- In the first column of the Sample Output Snapshot, you can see the DateTime Value returned by the GETDATE() function as a @Date local variable.
- In the second column named "AddedMiliSecond", you can see the milliseconds value has been added by 1500.
- In the third column named " AddedSeconds", 12 seconds have been added and resultant time is shown.
- In the fourth column named "AddedMinutes", 10 minutes added.
- Similarly, you can see in the last and fifth output column named "AddedHours", 3 hours have been added in the @Date value.
CONVERT()SQL Time function can be used. Here is how the
CONVERT()can be used in the above SELECT statement.
CONVERT()SQL Function is already used in the first example also.
DECLARE @Date datetime2 = GETDATE() SELECT CONVERT(Time,@Date) AS DATETIME_Value, CONVERT(Time, DATEADD(MILLISECOND,1500,@Date)) AS AddedMiliSecond, CONVERT(Time, DATEADD(SECOND,12,@Date)) AS AddedSeconds, CONVERT(Time, DATEADD(MINUTE,10,@Date)) AS AddedMinutes, CONVERT(Time, DATEADD(HOUR,3,@Date)) AS AddedHours;
Now to the problem is how to subtract the time in MS SQL Server with DATEADD() SQL function. It can be done with just a minor mathematical trick in the above used SELECT Query. There will be no change in syntax and the parameters. Just you will have to negate the value of the middle argument "Units" which is to be subtracted. For example in the above SELECT Statement, this will be the change.
DECLARE @Date datetime2 = GETDATE() SELECT CONVERT(Time,@Date) AS DATETIME_Value, CONVERT(Time, DATEADD(MILLISECOND,-1500,@Date)) AS SubMiliSecond, CONVERT(Time, DATEADD(SECOND,-12,@Date)) AS SubSeconds, CONVERT(Time, DATEADD(MINUTE,-10,@Date)) AS SubMinutes, CONVERT(Time, DATEADD(HOUR,-3,@Date)) AS SubHours;
You can observe that all middle values of the argument provide in DATEADD() Function, values are used with a negative sign to subtract this value from the time value. Another change is made after AS CLAUSE. Now the name of the columns is started with "Sub" to mentioned Subtracted.
7. How to use DATEDIFF() Function to Calculate difference of Two Times in SQL Server
Like ADDTME(), in MySQL TIMEDIFF() is used to find the difference of two times. But in Microsoft SQL Server, there is no dedicated SQL Time function for performing the subtraction of one time-value from another time-value.
DATEDIFF() function is used with DATEPART arguments to retrieve the difference to two time-values stores in the database. We will see here the different
DATEDIF() Examples to understand its use to compare the difference between two date values and especially how to get the difference of two time values.
It is to be noted that how to use SQL Server
DATEDIFF() SQL TIME function can be used to calculate the number of hours, minutes, seconds, microseconds, years, months, weeks, days, etc., between two DateTime values. Use of this function for the calculation of date has already been explained in our previous article "SQL Date Functions". Here our objective will be its use for comparison of time values and to calculate the number of hours, minutes, seconds and microseconds, etc.
DATEDIFF(interval, datetime1, datetime2)
This will subtract the datetime1 from datetime2 and return the difference based on the first argument interval. Again interval means the in which part of the DateTime you want to calculate the difference. A list of date_part argument list which can be used as "interval" is as under:
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
- microsecond, mcs = Microsecond
- nanosecond, ns = Nanosecond
Suppose you want to calculate the duration in hours, minutes, and seconds between 31-12-2019 and the CURRENT Date of the system. It was to remind you that on 31 December 2019, WHO was informed of cases of pneumonia of unknown cause in Wuhan City, China. A novel coronavirus was identified as the cause by Chinese authorities on 7 January 2020 and was temporarily named “2019-nCoV”.
Here is the SELECT Statement:
DECLARE @StartDate datetime2 = GETDATE() DECLARE @EndDate datetime2='2019/12/31 12:45:31.348902' SELECT DATEDIFF(hour, @EndDate, @StartDate) AS HoursDiff, DATEDIFF(minute, @EndDate, @StartDate) AS MinutesDiff, DATEDIFF(second, @EndDate, @StartDate) AS SecondsDiff, DATEDIFF_BIG(ms, @EndDate, @StartDate) AS MicroSecDiff;
You may see that the syntax is similar to DATEADD() SQL Function. DATEDIFF_BIG is used to calculate the difference in milliseconds because the value returned can overflow. DECLARE statement is used to declare the two dates.
HoursDiff column in the Sample output snapshot shows the difference in hours between @Startdate and @Enddate mentioned in the SELECT Statement. Similarly, MinutesDiff, SecondsDiff, MicroSecDiff show the difference in minutes, seconds, and microseconds respectively.
8. SEC_TO_TIME(), TIME_TO_SEC(), TO_SECONDS() SQL Time Functions
These are less frequently used SQL Time Functions in MySQL. As the name describes that these are used to convert the time-value into seconds and vice versa.
TIME_to_SEC() both are reciprocal to each other. The
SEC_TO_TIME() converts the seconds into time and
TIME_TO_SEC does the reverse.
TO_SECONDS() SQL time function return the total number of seconds since the year 0. For dates before 1582 (year 0), results from this function are not reliable. See Section 12.9, “What Calendar Is Used By MySQL?”, for details.
Let's apply this in the code example to check the results in the sample output. We will convert the CURRENT_DATE into Seconds and the same values of SECONDS will be passed as an argument to recalculate it to CURRENT TIME.
SELECT CURRENT_TIME AS TimeValue, TIME_TO_SEC(CURRENT_TIME) AS Time2Sec, SEC_To_TIME(TIME_TO_SEC(CURRENT_TIME)) AS Sec2Time, TO_SECONDS(CURRENT_TIME) AS Time2Seconds;
- Here in Sample Output 11, the first column "TimeValue" is showing the valued returned by CURRENT_TIME which is 13:24:10.
- In the second column named "Time2Sec", the above time-value is converted into seconds which is 48250.
- In the third column named "Sec2Time", again
TIME_TO_SEC(CURRENT_TIME)is passed an argument to SEC_TO_TIME() Function to convert the previously calculated valued 48250 to Time. It can be observed that the same time 12:24:10 is returned by this SQL Time function.
- In the fourth column, Time2Seconds is showing the output of TO_SECONDS(CURRENT_TIME) which is 63798067450 seconds since the year 0.
The SQL Time Functions are widely used by database developers in the manipulation of time. Date and Time manipulation are difficult due to different versions of SQL Time Functions with their synonyms in different database management systems like MySQL and Microsoft SQL Server. When you need to perform some arithmetic and logical operations on stored time values, a variety of SQL Time Functions are available there to help you.
In this article we have tried to explain all frequently used and non frequently used SQL Time functions like