Working with Dates in SQL in the LDP

• Date data types
  
  
  • Text - a date in Text data type 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 - 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

• • 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
    • 2022-11-25 15:05:27.764 -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
    
    

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

You can do the same thing when extracting a date from a data array:

json_extract_path_text (ffa.data,'metadata','createdDate') as raw_date (will be TEXT data type)

json_extract_path_text (ffa.data,'metadata','createdDate')::DATE as simple_date,

json_extract_path_text (ffa.data,'metadata','createdDate')::TIMESTAMP as date_with_time,

json_extract_path_text (ffa.data,'metadata','createdDate')::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" data type. 
    

    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
    
    TO_CHAR (json_extract_path_text (ffa.data,'metadata','createdDate')::TIMESTAMP,'mm/dd/yyyy hh:mi am') as date_with_hours_and_minutes_am_pm
    

• • Note that the TO_CHAR function converts the date to TEXT (it will no longer function as a date)

• Extracting Date Parts

-- When you want the month number (1-12) or weekday number (1-7), use the "Extract" function OR the "date_part" function 

-- When you want the month name or weekday name, use the "to_char" function.

-- When you want the numerical day of the month (1-31), use the "date_part" function

date_part ('month', li.loan_date) as month_number_using_date_part

date_part ('day', li.loan_date) as day_of_month_number_using_date_part

date_part ('dow', li.loan_date) as day_of_week_number_using_date_part

to_char (li.loan_date, 'Mon') as month_name_using_tochar

to_char (li.loan_date, 'Dy') as day_name_using_tochar

extract (month FROM li.loan_date) as month_number_using_extract

extract (day FROM li.loan_date) as day_of_month_number_using_extract

extract (dow FROM li.loan_date) as day_of_week_number_using_extract

• Finding the elapsed time between two dates

You can use the "AGE" function or date subtraction to find the time elapsed between two dates. In these example, we are using "CURRENT_DATE" as one of the dates, but you can use any two date fields, cast as the same data type.

AGE (CURRENT_DATE, table.field_name) AS time_elapsed

CURRENT_DATE::DATE - table.field_name::DATE AS days_elapsed

• • The Age function will show the results broken down into time components: Example: 10 mons 2 days 07:15:12.524
  • The date subtraction function will show the number of days, if the two dates you're subtracting are cast as "DATE": Example: 309
  • When the dates you're subtracting are in another date format (such as timestamptz), the results will show time components, which may be useful (or not). Example: 308 days 07:15:12.524