The PostgreSQLTO_TIMESTAMP() function converts a string to atimestamp according to the specified format.

Syntax

The following illustrates the syntax ofTO_TIMESTAMP() function:

TO_TIMESTAMP(timestamp, format)

Arguments

TheTO_TIMESTAMP() function requires two arguments:

1)timestamp

Thetimestamp is a string that represents a timestamp value in the format specified byformat.

2)format

The format fortimestamp argument.

To constructformat strings, you use the following template patterns for formatting date and time values.

Return Value

The PostgreSQLTO_TIMESTAMP() function returns a timestamp with the time zone.

Examples

The following statement uses theTO_TIMESTAMP() function to convert a string to a timestamp:

SELECT TO_TIMESTAMP(    '2017-03-31 9:30:20',    'YYYY-MM-DD HH:MI:SS');

Output:

to_timestamp------------------------ 2017-03-31 09:30:20-07(1 row)

In this example:

• YYYY is the four-digit year 2017
• MM is the month 03
• DD is the day 31
• HH is the hour 9
• MI is the minute 30
• SS is the second 20

Remarks

1) TheTO_TIMESTAMP() function skips spaces in the input string unless the fixed format global option (FX prefix) is used.

This example uses multiple spaces in the input string:

SELECT    TO_TIMESTAMP('2017     Aug','YYYY MON');

TheTO_TIMESTAMP() function just omits the spaces and returns the correct timestamp value:

to_timestamp------------------------ 2017-08-01 00:00:00-07(1 row)

However, the following example returns an error:

SELECT    TO_TIMESTAMP('2017     Aug','FXYYYY MON');

Output:

ERROR:  invalidvalue "" for "MON"DETAIL:  The givenvalue didnot match any of the allowedvalues for this field.SQL state:22007

Because theFX option instructs theTO_TIMESTAMP() to accept the input string with one space only.

2) TheTO_TIMESTAMP() function validates the input string with minimal error checking. It will try to convert the input string to a valid timestamp as much as possible that sometimes yields unexpected results.

The following example uses an invalid timestamp value:

SELECT    TO_TIMESTAMP('2017-02-31 30:8:00', 'YYYY-MM-DD HH24:MI:SS');

It returns an error:

ERROR:date/time fieldvalue out ofrange:"2017-02-31 30:8:00"SQL state:22008

3) When converting a string to a timestamp, theTO_TIMESTAMP() function treats milliseconds or microseconds as seconds after the decimal point.

SELECT    TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');

The result is:

to_timestamp-------------------------- 2017-01-01 00:00:10.2-07

In this example, 2 is not 2 milliseconds but 200. It means that:

SELECT        TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');

and

SELECT        TO_TIMESTAMP('01-01-2017 10:200','DD-MM-YYYY SS:MS');

returns the same result.

2017-01-01 00:00:10.2-07

To get 2 milliseconds, you must use01-01-2017 10:002. In this case,002 is interpreted as0.002 seconds, equivalent to 2 milliseconds.

4) If the year is less than four digits, theTO_TIMESTAMP() will adjust it to the nearest year e.g., 99 becomes 1999, 17 becomes 2017.

SELECT    TO_TIMESTAMP('12 31 99 12:45', 'MMDD YY HH:MI');

Output:

to_timestamp------------------------ 1999-12-31 00:45:00-07(1 row)

Consider the following example:

SELECT      TO_TIMESTAMP('12 31 16 12:45', 'MMDD YY HH:MI');

The nearest year of 16 is 2016, therefore, it returns the following result:

to_timestamp------------------------ 2016-12-31 00:45:00-07

In this tutorial, you have learned how to use the PostgreSQLTO_TIMESTAMP() function to convert a string to a timestamp.

Last updated onFebruary 12, 2024

On this page

• Syntax
• Arguments
• Return Value
• Examples
• Remarks