FORMAT_TIMESTAMP

​

Overview

The FORMAT_TIMESTAMP() function returns a given timestamp value in a specified format. Its syntax is illustrated below:

FORMAT_TIMESTAMP(timestamp, format_string)

This function requires two arguments, i.e., a timestamp string that represents the timestamp value that needs to be converted to a specified format and a format_string that specifies the format to be converted into. Its return type is a timestamp value with a timezone.

​

#Case 1: Basic FORMAT_TIMESTAMP() function

The below example uses the FORMAT_TIMESTAMP() function to convert a given timestamp into a timestamp format as specified in the function arguments.

SELECT FORMAT_TIMESTAMP( 2 '2022-05-30 5:30:04', 3 'YYYY-MM-DD HH:MI:SS' 4);

Details of the format specified are as follows:

• YYYY is the four-digit year 2022

• MM is the month: 05

• DD is the day: 30

• HH is the hour: 5

• MI is the minute: 30

• SS is the second: 04

The final output will be as follows:

+-----------------------------+
| format_timestamp            |
+-----------------------------+
| 2022-05-30 05:30:04+05      |
+-----------------------------+

​

#Case 2: FORMAT_TIMESTAMP() function using multiple spaces

The FORMAT_TIMESTAMP() when given multiple spaces in the input string, omits the spaces and only returns the correct timestamp value. Let’s see how it works using the following example:

SELECT 2 FORMAT_TIMESTAMP('2008 Dec','YYYY MON');

It will return the following output:

+-----------------------------+
| format_timestamp            |
+-----------------------------+
| 2008-12-01 00:00:00+05      |
+-----------------------------+

​

#Case 3: FORMAT_TIMESTAMP() function if the input value of the year is less than 4 digits

FORMAT_TIMESTAMP() will adjust the year to the nearest year value if the input argument has less than the required number of digits i.e., less than 4. To see how it works, look at the example below:

SELECT 2 FORMAT_TIMESTAMP('07 25 09 10:40', 'MM DD YY HH:MI');

It will return the following output:

+-----------------------------+
| format_timestamp            |
+-----------------------------+
| 2009-07-25 10:40:00+06      |
+-----------------------------+

In this example, the two-digit year 09 has been changed to the nearest four-digit year i.e., 2009. Similarly, 70 will become 1970, and 10 will become 2010, etc.