PervasiveSQL Database dates conversion

Question

I'm working on tables obtained from a PervasiveSQL database and I have some trouble managing dates. In some of the fields dates are recorded in the format we use in Italy, dd/mm/yyyy, but in others are recorded in a format I can't understand, something like this: Start_Date 132384788
Last_Tx_Date 132385052
Last_Tx_Time 252711936 What kind of format is it? How can I convert it in a human readable one? I think that Start_Date could be August 8 2020 but I'm not sure. Thanks for any help!

I tried to copy and paste tables in an Excel file but automatic dates conversion did not work.

Answer 1

The Start_Date and Last_Tx_Date fields look to be Btrieve Date fields. If you set the data type for that field in the DDFs to Date, it should show a human readable field. However the Last_Tx_Time field is a Btrieve Time (not timestamp) type.
From the Actian Zen v15.10 documentation (https://docs.actian.com/zen/v15/#page/sqlref/sqldtype.htm#ww136646):

Date: The DATE key type is stored internally as a 4-byte value. The day and the month are each stored in 1-byte binary format. The year is a 2-byte binary number that represents the entire year value. The MicroKernel places the day into the first byte, the month into the second byte, and the year into a two-byte word following the month. An example of C structure used for date fields would be:

TYPE dateField {
char day;
char month;
integer year;
}

The year portion of a date field is expected to be set to the integer representation of the entire year. For example, 2,001 for the year 2001.

Time: The TIME key type is stored internally as a 4-byte value. Hundredths of a second, second, minute, and hour values are each stored in 1-byte binary format. The MicroKernel places the hundredths of a second value in the first byte, followed respectively by the second, minute, and hour values. The data format is hh:mm:ss.nn. Supported values range from 00:00:00.00 to 23:59:59.99.