You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 3
Next »
- 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
- Date - 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
- 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
- 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
- Finding the elapsed time between two dates