String to Date and Timestamp

The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date.

The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text).

Usage examples:

Date as inputFunction Call
2017/02/08TO_DATE(date, 'YYYY/MM/DD')
08/02/2017TO_DATE(date, 'DD/MM/YYYY')
08-02-2017TO_DATE(date, ‘DD-MM-YYYY’’)
02082017TO_DATE(date', 'MMDDYY')
February 08, 2017TO_DATE(date', 'Month DD, YYYY')

 

Datetime as inputFunction call
02/08/2017 10:59:00TO_TIMESTAMP(datetime, 'MM/DD/YYYY HH24:MI:SS') or TO_TIMESTAMP(datetime, 'MM/DD/YYYY HH12:MI:SS') if in 12h format
2017/08/02 10:59:00TO_TIMESTAMP(datetime, ‘YYYY/MM/DD HH24:MI:SS') or TO_TIMESTAMP(datetime, ‘YYYY/MM/DD HH12:MI:SS') if in 12h format
08-02-2017 10:59:00TO_TIMESTAMP(datetime, ‘DD-MM-YYYY HH24:MI:SS') or TO_TIMESTAMP(datetime, ‘DD-MM-YYYY HH12:MI:SS') if in 12h format
10:15:20TO_TIMESTAMP(datetime, 'HH24:MI:SS')::TIME or TO_TIMESTAMP(datetime, 'HH12:MI:SS')::TIME if in 12h format

String to numeric

In PostgreSQL strings can be converted to integer or double using CAST function or the :: annotation. Both are equivalent and you can choose whichever you like.

Usage example:

Number as inputFunction call
5CAST(number AS int) or alternatively number::int
2.3CAST(number AS double precision) or alternatively number::double precision

If a column contains money data you should keep in mind that floating point numbers should not be used to handle money due to the potential for rounding errors.

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

Number as inputFunction call
13.4Number::money::numeric::float8

Number::moneyAvoid converting it to float due to potential rounding errors

 

in SQL Playbook