How the LOCALTIME() function works in Mariadb?

The LOCALTIME() function is a date and time function that returns the current date and time in the local time zone of the server. It is equivalent to the NOW() function, except that it returns a DATETIME value instead of a TIMESTAMP value. The LOCALTIME() function can also accept an optional argument that specifies the precision of the fractional seconds. The default precision is 0, which means no fractional seconds are returned.

Syntax

The syntax of the LOCALTIME() function is as follows:

LOCALTIME([precision])

The precision argument is an integer value that ranges from 0 to 6. It determines the number of digits after the decimal point in the fractional seconds part of the returned value. If the precision argument is omitted or invalid, it is treated as 0.

Examples

Example 1: Basic usage of the LOCALTIME() function

The following example shows how to use the LOCALTIME() function without any argument. It returns the current date and time in the local time zone of the server, with no fractional seconds.

SELECT LOCALTIME();

The output is:

2024-02-13 09:08:57

Example 2: Using the precision argument

The following example shows how to use the LOCALTIME() function with a precision argument of 3. It returns the current date and time in the local time zone of the server, with 3 digits of fractional seconds.

SELECT LOCALTIME(3);

The output is:

2024-02-13 09:08:57.123

Example 3: Using the precision argument with a value greater than 6

The following example shows what happens when the LOCALTIME() function is used with a precision argument of 7, which is greater than the maximum allowed value of 6. The function returns the current date and time in the local time zone of the server, with 6 digits of fractional seconds, and a warning message.

SELECT LOCALTIME(7);

The output is:

2024-02-13 09:08:57.123456

The warning message is:

Warning: 1265 Data truncated for column '%s' at row %ld

Related Functions

There are some other functions that are related to the LOCALTIME() function in Mariadb. They are:

• NOW(): This function returns the current date and time as a TIMESTAMP value. It is equivalent to the LOCALTIME() function, except that it returns a different data type. It can also accept a precision argument, just like the LOCALTIME() function.
• CURDATE(): This function returns the current date as a DATE value. It is equivalent to the LOCALTIME() function, except that it only returns the date part, not the time part.
• CURTIME(): This function returns the current time as a TIME value. It is equivalent to the LOCALTIME() function, except that it only returns the time part, not the date part. It can also accept a precision argument, just like the LOCALTIME() function.
• UTC_TIMESTAMP(): This function returns the current date and time as a TIMESTAMP value in the UTC time zone. It is similar to the NOW() function, except that it returns a different time zone. It can also accept a precision argument, just like the NOW() function.

Here are some examples of using these related functions:

-- Get the current date and time as a TIMESTAMP value
SELECT NOW();

-- Get the current date and time as a TIMESTAMP value with 4 digits of fractional seconds
SELECT NOW(4);

-- Get the current date as a DATE value
SELECT CURDATE();

-- Get the current time as a TIME value
SELECT CURTIME();

-- Get the current time as a TIME value with 2 digits of fractional seconds
SELECT CURTIME(2);

-- Get the current date and time as a TIMESTAMP value in the UTC time zone
SELECT UTC_TIMESTAMP();

-- Get the current date and time as a TIMESTAMP value in the UTC time zone with 5 digits of fractional seconds
SELECT UTC_TIMESTAMP(5);

Conclusion

In this article, we have learned how the LOCALTIME() function works in Mariadb. We have seen its syntax, examples, and related functions. We have also learned how to use the optional precision argument to control the fractional seconds part of the returned value. The LOCALTIME() function is a useful function to get the current date and time in the local time zone of the server. It can be used for various purposes, such as logging, auditing, scheduling, etc.