How the LOCALTIMESTAMP() function works in Mariadb?

The LOCALTIMESTAMP() function is a date and time function that returns the current date and time as a TIMESTAMP value in the local time zone of the server. It is equivalent to the CURRENT_TIMESTAMP function, except that it can 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 LOCALTIMESTAMP() function is as follows:

LOCALTIMESTAMP([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 LOCALTIMESTAMP() function

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

SELECT LOCALTIMESTAMP();

The output is:

2024-02-13 09:08:57

Example 2: Using the precision argument

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

SELECT LOCALTIMESTAMP(2);

The output is:

2024-02-13 09:08:57.12

Example 3: Using the precision argument with a negative value

The following example shows what happens when the LOCALTIMESTAMP() function is used with a precision argument of -1, which is a negative value. The function returns the current date and time as a TIMESTAMP value in the local time zone of the server, with no fractional seconds, and a warning message.

SELECT LOCALTIMESTAMP(-1);

The output is:

2024-02-13 09:08:57

The warning message is:

Warning: 1264 Out of range value for column '%s' at row %ld

Related Functions

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

• CURRENT_TIMESTAMP: This function returns the current date and time as a TIMESTAMP value in the local time zone of the server. It is equivalent to the LOCALTIMESTAMP() function, except that it does not accept any argument. It always returns the value with the default precision of 0.
• LOCALTIME: This function returns the current date and time as a DATETIME value in the local time zone of the server. It is similar to the LOCALTIMESTAMP() function, except that it returns a different data type. It can also accept a precision argument, just like the LOCALTIMESTAMP() 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 LOCALTIMESTAMP() function, except that it returns a different time zone. It can also accept a precision argument, just like the LOCALTIMESTAMP() function.

Here are some examples of using these related functions:

-- Get the current date and time as a TIMESTAMP value with the default precision
SELECT CURRENT_TIMESTAMP;

-- Get the current date and time as a DATETIME value
SELECT LOCALTIME();

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

-- 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 3 digits of fractional seconds
SELECT UTC_TIMESTAMP(3);

Conclusion

In this article, we have learned how the LOCALTIMESTAMP() 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 LOCALTIMESTAMP() function is a useful function to get the current date and time as a TIMESTAMP value in the local time zone of the server. It can be used for various purposes, such as logging, auditing, scheduling, etc.