I’m getting an ‘Unacceptable timestamp string specified’ error when inserting a row on a GridDB container

Question

Question: I’m getting an ‘Unacceptable timestamp string specified’ error when inserting a row on a GridDB container I’m running GridDB version 5.5.0-40191 CE on Windows 11 WSL, with Ubuntu installed. I’m trying to insert lines in a table using CLI (gs_sh). The table layout is shown below:

CREATE TABLE DimEmployee (
    EmployeeKey INTEGER NOT NULL PRIMARY KEY ,
    ParentEmployeeKey INTEGER NULL,
    EmployeeNationalIDAlternateKey STRING NULL,
    ParentEmployeeNationalIDAlternateKey STRING NULL,
    SalesTerritoryKey INTEGER NULL,
    FirstName STRING NOT NULL,
    LastName STRING NOT NULL,
    MiddleName STRING NULL,
    NameStyle BOOL NOT NULL,
    Title STRING NULL,
    HireDate TIMESTAMP NULL,
    BirthDate TIMESTAMP NULL,
    LoginID STRING NULL,
    EmailAddress STRING NULL,
    Phone STRING NULL,
    MaritalStatus STRING NULL,
    EmergencyContactName STRING NULL,
    EmergencyContactPhone STRING NULL,
    SalariedFlag BOOL NULL,
    Gender STRING NULL,
    PayFrequency BYTE NULL,
    BaseRate float NULL,
    VacationHours SHORT NULL,
    SickLeaveHours SHORT NULL,
    CurrentFlag BOOL NOT NULL,
    SalesPersonFlag BOOL NOT NULL,
    DepartmentName STRING NULL,
    StartDate TIMESTAMP NULL,
    EndDate TIMESTAMP NULL,
    Status STRING NULL,
    EmployeePhoto BLOB NULL );

When I run the following SQL INSERT statement, I get a ‘Unacceptable timestamp string specified’ error.

INSERT INTO DimEmployee VALUES (6,267,'480168528',NULL,11,'Thierry','D''Hers','B',false,'Tool Designer',TIMESTAMP('2007-07-11T00:00:00.0
00Z'),TIMESTAMP('1959-02-26T00:00:00.000Z'),'motor-works\thierry0','thierry0@motor-works.com','168-555-0183','M','Thierry D''Hers','168-555-
0183',false,'M',2,25.00,9,24,true,false,'Tool Design',TIMESTAMP('2007-07-11T00:00:00.000Z'),NULL,'Current',NULL);

The full error message is shown below:

D20332: An unexpected error occurred while executing a SQL. : msg=[[305005:SQL_PROC_VALUE_SYNTAX_ERROR] Failed to evaluate constant expression (reason=Unacceptable timestamp string specified (value=1959-02-26T00:00:00.000Z) (reason=Internal error by unexpected utility problem (reason=Time fields out of range))) on updating (sql="insert INTO DimEmployee VALUES (6,267,'480168528',NULL,11,'Thierry','D''Hers','B',false,'Tool Designer',TIMESTAMP('2007-07-11T00:00:00.000Z'),TIMESTAMP('1959-02-26T00:00:00.000Z'),'motor-worksthierry0','thierry0@motor-works.com','168-555-0183','M','Thierry D''Hers','168-555-0183',false,'M',2,25.00,9,24,true,false,'Tool Design',TIMESTAMP('2007-07-11T00:00:00.000Z'),NULL,'Current',NULL)") (db='public') (user='admin') (appName='gs_sh') (clientId='9ee7a962-e2cd-47ed-a5b9-d0867d9389db:91') (source={clientId=5, address=127.0.0.1:38326}) (address=127.0.0.1:20001, partitionId=1084)]

Answer 1

According to the document GridDB_NewSQL_SQL_Reference.pdf, available here, a TIMESTAMP column will convert the original value to a number of milliseconds from January 1 1970 00:00:00 GMT, a format known as UNIX Epoch Time. Therefore, the value of 1959-02-26T00:00:00.000Z for BirthDate cannot be stored in that column.

One workaround is to store BirthDate as a string column, and later convert the stored value to datetime on the client side. A drawback of this approach is that an invalid date, such as Feb, 30th, could be stored and the database system would not be able to catch the invalid value.

Another workaround is to store BirthDate as a 64-bit INTEGER column (LONG) containing the number of milliseconds from January 1 1970 00:00:00 GMT. For example, the date ‘1959-02-26T00:00:00.000Z’ would be stored as -342316800000.

Converting a datetime value to LONG, and back, can be easily done in Python:

from datetime import datetime
# Convert the string to a datetime object
input_datetime = "1959-02-26T00:00:00.000Z"

# Calculate Unix time in milliseconds
time_difference =datetime.strptime(input_datetime, "%Y-%m-%dT%H:%M:%S.%fZ") - datetime(1970, 1, 1)
unix_time_milliseconds = int(time_difference.total_seconds() * 1000) 

# Output the results
print(f"Unix Epoch Time (milliseconds): {unix_time_milliseconds}"

Likewise, if the date is coming from a SQL Server database, the conversion to Unix Epoch can also be easily done with the following code:

DECLARE @datetime DATETIME = '1959-02-26 00:00:00.000'; -- Your datetime value
-- Convert to Unix Epoch Time in milliseconds
SELECT convert (bigint, DATEDIFF(SECOND, '1970-01-01 00:00:00', @datetime)) * 1000 
       + DATEPART(MILLISECOND, @datetime) AS unix_epoch_time_ms;