Cloud

format_timestamp

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

FORMAT_TIMESTAMP(timestamp, format_string)

This function requires two arguments:

  • timestamp: A string representing the timestamp value that needs to be converted to a specified format.

  • format_string: A string specifying the format to be converted into.

Its return type is a timestamp value with a timezone.

Basic format_timestamp() function

This example shows how to use the format_timestamp() function to convert a given timestamp into a timestamp format as specified in the function arguments.

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

Details of the format specified:

  • 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 format specified in the string can be used in any combination.

The query returns:

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

format_timestamp() function using multiple spaces

This example shows how the format_timestamp() function handles multiple spaces in the input string. When given multiple spaces, it omits the spaces and only returns the correct timestamp value:

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

This returns the output:

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

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 (for example, less than 4). This example shows how it works:

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

This returns the 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 (for example, 2009). Similarly, 70 will become 1970, and 10 will become 2010.