Dates and Times in Octo

K.S. Bhaskar

Octo now supports dates and times.

While the ability to store and process dates and times is essential to many data processing applications, they are perhaps the least standard basic functionality across SQL implementations, as shown by the following table from SQL Date and Time.

ISO 8601 is an international standard for dates and times that SQL implementations support.

Applications can have specialized needs for dates. For example, medical applications need to store imprecise dates, like “July 1978”, or just “1978” (for example, I know that my tonsils were removed in 1958, but I have no idea when in 1958). Fileman dates allow for storing dates with arbitrary levels of (im)precision. Specialized dates result in ad hoc implementations of dates when using SQL to access, e.g., as VARCHAR, INTEGER or NUMERIC types.

Octo provides several date and time types:

• DATE
• TIME [WITHOUT TIME ZONE]
• TIMESTAMP [WITHOUT TIME ZONE]
• TIME WITH TIME ZONE
• TIMESTAMP WITH TIME ZONE.

[WITHOUT TIME ZONE] means that the text is optional.

Formats that Octo supports are:

• TEXT (values such as ‘2023-01-01′ and ’01:01:01’)
• HOROLOG (values in  $HOROLOG format)
• ZHOROLOG (values in $ZHOROLOG format)
• ZUT (integers interpreted as $ZUT values)
• FILEMAN (numeric values of the form YYYMMDD.HHMMSS), where
  • YYY is year since 1700 with 000 not allowed
  • MM is month; two digits 01 through 12
  • DD is the day of the month; two digits 01 through 31
  • HH is the hour; two digits, 00 through 23
  • MM is the minute; two digits, 00 through 23
  • SS is second; two digits, 00 through 59

Here is an example of a query using Fileman dates against a VistA VeHU Docker image, which has simulated patient data for training purposes which you can run and experiment with.

OCTO> SELECT P.NAME AS PATIENT_NAME, P.PATIENT_ID as PATIENT_ID, P.DATE_OF_BIRTH, P.Age, P.WARD_LOCATION, PM.DATE_TIME as Admission_Date_Time,
      TOKEN(REPLACE(TOKEN(REPLACE(P.WARD_LOCATION,'WARD ',''),'-',1),'WARD ',''),' ',2) AS PCU,
      CONCAT(TOKEN(REPLACE(TOKEN(REPLACE(P.WARD_LOCATION,'WARD ',''),'-',2),'WARD ',''),' ',1),' ',TOKEN(P.WARD_LOCATION,'-',3)) AS UNIT,
      P.ROOM_BED as ROOM_BED, REPLACE(P.DIVISION,'VEHU ','') as FACILTY, P.SEX as SEX, P.CURRENT_ADMISSION as CURRENT_ADMISSION,
      P.CURRENT_MOVEMENT as CURRENT_MOVEMENT, PM.PATIENT_MOVEMENT_ID as Current_Patient_Movement,
      PM.TYPE_OF_MOVEMENT as Current_Movement_Type, AM.PATIENT_MOVEMENT_ID as Admission_Movement,
      AM.TYPE_OF_MOVEMENT as Admission_Type
FROM PATIENT P
LEFT JOIN patient_movement PM ON P.CURRENT_MOVEMENT=PM.PATIENT_MOVEMENT_ID
LEFT JOIN patient_movement AM ON P.CURRENT_ADMISSION=AM.PATIENT_MOVEMENT_ID
WHERE P.CURRENT_MOVEMENT IS NOT NULL
AND P.ward_location NOT LIKE 'ZZ%'
AND P.NAME NOT LIKE 'ZZ%'
AND PM.DATE_TIME > timestamp'2015-01-01 00:00:00' LIMIT 5;
patient_name|patient_id|date_of_birth|age|ward_location|admission_date_time|pcu|unit|room_bed|facilty|sex|current_admission|current_movement|current_patient_movement|current_movement_type|admission_movement|admission_type
ONEHUNDRED,PATIENT|100013|1935-04-07|89|ICU/CCU|2015-09-10 08:38:18|| |ICU-10|DIVISION|M|4686|4686|4686|1|4686|1
TWOHUNDREDSIXTEEN,PATIENT|100162|1935-04-07|89|7A GEN MED|2016-06-26 20:24:39|GEN| ||CBOC|M|4764|4764|4764|1|4764|1
ONEHUNDREDNINETYSIX,PATIENT|100296|1935-04-07|89|7A GEN MED|2015-09-25 11:56:03|GEN| ||CBOC|M|4711|4711|4711|1|4711|1
ZERO,INPATIENT|100709|1945-03-09|79|7A SURG|2015-04-04 13:38:10|SURG| |775-A|CBOC|M|4672|4672|4672|1|4672|1
EIGHT,INPATIENT|100716|1945-03-09|79|3E NORTH|2015-04-03 11:25:45|NORTH| |3E-100-1|CBOC|M|4636|4636|4636|1|4636|1
(5 rows)
OCTO>

If your application has specialized dates, we invite you to discuss your requirements with us, so that we can extend Octo to meet your needs.

1. Photo of Mayan calendar stone fragment from the Classical Period (550-800 CE) at Ethnologiches Museum Berlin. Credit: José Luis Filpo Cabana, used under the Creative Commons Attribution-Share Alike 4.0 International license.
2. Photo of one of NIST’s ytterbium lattice atomic clocks. NIST physicists combined two of these experimental clocks to make the world’s most stable single atomic clock. The image is a stacked composite of about 10 photos in which an index card was positioned in front of the lasers to reveal the laser beam paths. Credit: N. Phillips/NIST

Published on August 14, 2024

See All Product News See All What's New