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.
      • Example: 2022-08-15
    • 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


  • Date Formatting


  • Extracting Date Parts


  • Finding the elapsed time between two dates




  • No labels