How to use the MySQL TIMESTAMP() function

In this article, we will learn how to use the MySQL TIMESTAMP() function, which returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value.

Posted on

In this article, we will learn how to use the MySQL TIMESTAMP() function, which returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with timestamps and values.

Syntax

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

TIMESTAMP(expr1, expr2)

The expr1 parameter can be any valid date, datetime, or timestamp expression, or a string that can be converted to a date, datetime, or timestamp value. The expr2 parameter is optional, and can be any valid time expression, or a string that can be converted to a time value. If the expr1 parameter is NULL, the function returns NULL. If the expr2 parameter is omitted or NULL, the function returns the timestamp value of the expr1 parameter. If both parameters are given, the function returns the timestamp value that is the result of adding the time value expr2 to the date or datetime value expr1. The format of the timestamp value is ‘YYYY-MM-DD HH:MM:SS’. For example, TIMESTAMP('2023-01-15', '10:02:34') returns ‘2023-01-15 10:02:34’, which is the timestamp value of the date and time values.

Examples

Let’s see some examples of how to use the TIMESTAMP() function in MySQL.

Example 1: Get the timestamp value of a date value

We can use the TIMESTAMP() function to get the timestamp value of a date value. For example:

SELECT TIMESTAMP('2023-01-15') AS result;

This query will return the timestamp value of the date value ‘2023-01-15’. The query will return ‘2023-01-15 00:00:00’, which is the timestamp value of the date value with the default time.

Example 2: Get the timestamp value of a datetime value

We can use the TIMESTAMP() function to get the timestamp value of a datetime value. For example:

SELECT TIMESTAMP('2023-01-15 10:02:34') AS result;

This query will return the timestamp value of the datetime value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 10:02:34’, which is the same as the datetime value.

Example 3: Get the timestamp value of a timestamp value

We can use the TIMESTAMP() function to get the timestamp value of a timestamp value. For example:

SELECT TIMESTAMP('2023-01-15 10:02:34') AS result;

This query will return the timestamp value of the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 10:02:34’, which is the same as the timestamp value.

Example 4: Get the timestamp value of a string value

We can use the TIMESTAMP() function to get the timestamp value of a string value that can be converted to a date, datetime, or timestamp value. For example:

SELECT TIMESTAMP('January 15, 2023 10:02:34') AS result;

This query will return the timestamp value of the string value ‘January 15, 2023 10:02:34’ that can be converted to a datetime value. The query will return ‘2023-01-15 10:02:34’, which is the timestamp value of the datetime value.

Example 5: Get the timestamp value of adding a time value to a date or datetime value

We can use the TIMESTAMP() function to get the timestamp value of adding a time value to a date or datetime value. For example:

SELECT TIMESTAMP('2023-01-15', '10:02:34') AS result;

This query will return the timestamp value of adding the time value ‘10:02:34’ to the date value ‘2023-01-15’. The query will return ‘2023-01-15 10:02:34’, which is the timestamp value of the date and time values.

There are some other functions that are related to the TIMESTAMP() function, and can be useful for working with timestamps and values. Here are some of them:

  • TIME(): This function returns the time part of a date or datetime value, or a string that can be converted to a time value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
  • DATE(): This function returns the date part of a date or datetime value, or a string that can be converted to a date value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
  • DATETIME(): This function returns the datetime value of a date or datetime value, or a string that can be converted to a datetime value. For example, DATETIME('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
  • TIMESTAMPDIFF(): This function returns the difference between two timestamp values, in a specified unit. For example, TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') returns 60, which is the number of seconds between the two timestamp values.
  • TIMESTAMPADD(): This function returns the timestamp value that is the result of adding a specified interval to a timestamp value. For example, TIMESTAMPADD(MINUTE, 1, '2023-01-15 10:02:34') returns ‘2023-01-15 10:03:34’, which is the timestamp value after adding one minute.

Conclusion

In this article, we learned how to use the MySQL TIMESTAMP() function, which returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with timestamps and values.