- 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
- 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.
- 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
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
- 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.
- 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 - AGE, INTERVAL, simple subtraction
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
To find the number days elapsed since a given calendar date, use the subtraction function:
current_date -'2022-07-01' as number_of_days_since_7_1_22
To find a calendar date that is some time interval from another date, use the "INTERVAL" function in combination with subtraction (or addition, as needed):
to_char ((current_date - interval '33 years 9 months 26 days')::date,'mm/dd/yyyy') AS date_record_created
...result displays as a date: 03/19/1989 – because that's how we formatted the expression with TO_CHAR
age (current_date,'1989-03-17') AS amount_of_time_in_collection
...result displays as a time interval: 33 years 9 mons 26 days