- Date data types
- Text - a date in Text format may look like a date, complete with time components, but behaves as text. Usually contains a "T" before the time components. Text-type dates are present in many derived tables (LDP 1.4 or earlier). Note that the appearance may vary depending on the format of the date in the source table. IMPORTANT: dates extracted from a data array via "json_extract_path_text" will be in TEXT format. They must be converted to a date, timestamp or timestamptz data type to be useable in queries where you are using the date in inequalities (date A > date B), date component extractions or date calculation expressions.
- Example: instance_ext.record_created_date: 2021-06-18T18:04:00.85
- Text - a date in Text format may look like a date, complete with time components, but behaves as text. Usually contains a "T" before the time components. Text-type dates are present in many derived tables (LDP 1.4 or earlier). Note that the appearance may vary depending on the format of the date in the source table. IMPORTANT: dates extracted from a data array via "json_extract_path_text" will be in TEXT format. They must be converted to a date, timestamp or timestamptz data type to be useable in queries where you are using the date in inequalities (date A > date B), date component extractions or date calculation expressions.
- Date - this is a simple date that contains just the core date without time components. This data type is not found natively in the LDP, but is used to convert other data types into a form that is useful for querying.
- Example: 2022-08-15
- Date - this is a simple date that contains just the core date without time components. This data type is not found natively in the LDP, but is used to convert other data types into a form that is useful for querying.
- Timestamp - contains the date and time components, but with zeros where the timezone components would be.
- 2021-06-25T13:10:23.509+00:00
- Timestamp - contains the date and time components, but with zeros where the timezone components would be.
- Timestamptz - contains the core date and time components, plus the timezone offset (-0400 or -0500 for the New York timezone; varies because of daylight savings time (0400) vs. standard time (0500))
- 2022-05-04 14:03:23.554 -0400
- 2022-11-25 15:05:27.764 -0500
- Timestamptz - contains the core date and time components, plus the timezone offset (-0400 or -0500 for the New York timezone; varies because of daylight savings time (0400) vs. standard time (0500))
- Converting date types by "Casting"
- To convert a date to a different data type, use the "cast" function, represented by a double colon. Example: to cast a "timestamptz" date to a simple date, enter
table.field_name::DATE
To convert a "text" date to a timestamptz date:
table.field_name::TIMESTAMPTZ
- To convert a date to a different data type, use the "cast" function, represented by a double colon. Example: to cast a "timestamptz" date to a simple date, enter
cl.loan_date AS raw_date,
cl.loan_date::DATE AS simple_date,
cl.loan_date::TIMESTAMP AS date_with_time,
cl.loan_date::TIMESTAMPTZ AS date_with_timezone
- Date Formatting
- To format a date in standard eye-readable format in your output, you may want to convert the date using the "TO_CHAR" function. Syntax is: TO_CHAR (table.field::date type, 'format that you want to see it in'). If you want to show the time components, the date has to be cast in the "timestamp" format.
to_char (cl.loan_date::DATE,'mm/dd/yyyy') AS simple_date,
to_char (cl.loan_date::TIMESTAMP,'mm/dd/yyyy hh:mi am') AS date_with_time_and_am_pm
- Note that the TO_CHAR function converts the date to TEXT (it will no longer function as a date)
- To format a date in standard eye-readable format in your output, you may want to convert the date using the "TO_CHAR" function. Syntax is: TO_CHAR (table.field::date type, 'format that you want to see it in'). If you want to show the time components, the date has to be cast in the "timestamp" format.
- Extracting Date Parts
- Finding the elapsed time between two dates