Doesn't emulate timetz and time with time zone correctly

[ArsenyYankovsky]

Describe the bug
Currently the Data API will always convert every type containing a timezone into UTC. For example, if you save the value "15:30:00 PST" into a timetz or a time with time zone column and then query the same row it will return "23:30:00".

Local Data API, however, returns a string with timezone "15:30:00 PST".

[koxudaxi]

Thank you for the reports.
I will fix it.

[ArsenyYankovsky]

Thanks, looking forward to uncommenting some test cases :)

[koxudaxi]

@ArsenyYankovsky
I have pushed a fixed version as 0.6.5

[ArsenyYankovsky]

Unfortunately, I'm still getting the timezoned value back. Here are the queries I'm running:

CREATE TABLE "date_entity" ("id" SERIAL NOT NULL, "date" date NOT NULL, "interval" interval NOT NULL, "time" TIME NOT NULL, "timeWithTimeZone" TIME WITH TIME ZONE NOT NULL, "timetz" TIME WITH TIME ZONE NOT NULL, "timestamp" TIMESTAMP NOT NULL, "timestampWithTimeZone" TIMESTAMP WITH TIME ZONE NOT NULL, "timestamptz" TIMESTAMP WITH TIME ZONE NOT NULL, CONSTRAINT "PK_3b79937ff555c5865053d931f42" PRIMARY KEY ("id"));

INSERT INTO "date_entity"("date", "interval", "time", "timeWithTimeZone", "timetz", "timestamp", "timestampWithTimeZone", "timestamptz") VALUES (:param_1, :param_2, :param_3, :param_4, :param_5, :param_6, :param_7, :param_8) RETURNING "id" -- PARAMETERS: [{"name":"param_1","value":"2017-06-21","cast":"DATE"},{"name":"param_2","value":"1 year 2 months 3 days 4 hours 5 minutes 6 seconds","cast":"interval"},{"name":"param_3","value":"15:30:00","cast":"TIME"},{"name":"param_4","value":"15:30:00 PST","cast":"time with time zone"},{"name":"param_5","value":"15:30:00 PST","cast":"timetz"},{"name":"param_6","value":"2021-03-29 07:26:37","cast":"TIMESTAMP"},{"name":"param_7","value":"2021-03-29 07:26:37","cast":"TIMESTAMP"},{"name":"param_8","value":"2021-03-29 07:26:37","cast":"TIMESTAMP"}]

SELECT "DateEntity"."id" AS "DateEntity_id", "DateEntity"."date" AS "DateEntity_date", "DateEntity"."interval" AS "DateEntity_interval", "DateEntity"."time" AS "DateEntity_time", "DateEntity"."timeWithTimeZone" AS "DateEntity_timeWithTimeZone", "DateEntity"."timetz" AS "DateEntity_timetz", "DateEntity"."timestamp" AS "DateEntity_timestamp", "DateEntity"."timestampWithTimeZone" AS "DateEntity_timestampWithTimeZone", "DateEntity"."timestamptz" AS "DateEntity_timestamptz" FROM "date_entity" "DateEntity" WHERE "DateEntity"."id" IN (:param_1) -- PARAMETERS: [{"name":"param_1","value":1}]

Expected value in both the timeWithTimeZone and timetz columns is 23:30:00, but I'm getting back 15:30:00-08

[koxudaxi]

I'm sorry, I expected the behavior when the type is the timestamptz and timestampWithTimeZone.
I will add the converting method for timeWithTimeZone and timetz

[koxudaxi]

@ArsenyYankovsky
I have pushed a fixed version as 0.6.6
Would you please check it?

[ArsenyYankovsky]

@koxudaxi Thanks for a quick fix! Awesome work, it does work as intended.

However, I noticed a new bug in the 0.6.6 related to the MySQL engine.

I'm running the following queries:

CREATE TABLE `date_entity` (`id` int NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `datetime` datetime NOT NULL, `timestamp` timestamp NOT NULL, `time` time NOT NULL, `year` year NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

INSERT INTO `date_entity`(`id`, `date`, `datetime`, `timestamp`, `time`, `year`) VALUES (DEFAULT, :param_0, :param_1, :param_2, :param_3, :param_4) -- PARAMETERS: [{"name":"param_0","value":"2017-06-21","cast":"DATE"},{"name":"param_1","value":"2021-03-29 16:22:14","cast":"DATETIME"},{"name":"param_2","value":"2021-03-29 16:22:14","cast":"DATETIME"},{"name":"param_3","value":"15:30:00","cast":"TIME"},{"name":"param_4","value":2017}];

SELECT `DateEntity`.`id` AS `DateEntity_id`, `DateEntity`.`date` AS `DateEntity_date`, `DateEntity`.`datetime` AS `DateEntity_datetime`, `DateEntity`.`timestamp` AS `DateEntity_timestamp`, `DateEntity`.`time` AS `DateEntity_time`, `DateEntity`.`year` AS `DateEntity_year` FROM `date_entity` `DateEntity` WHERE `DateEntity`.`id` IN (:param_0) -- PARAMETERS: [{"name":"param_0","value":1}];

I'm getting the following in both timestamp and datetime columns from the local Data API:
"2021-03-29T14:22:14.000Z"

And this is what I'm getting from a real Data API:
""2021-03-29T16:22:14.000Z"

[koxudaxi]

@ArsenyYankovsky
Thank you for testing it.
But, I can't reproduce the problem.

I tried some queries... I got the correct results.
Would please share your test code? Or, more examples.

$ aws rds-data execute-statement --database 'test' \
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
    --include-result-metadata \
    --sql $'CREATE TABLE `dt` ( `datetime` datetime NOT NULL) ENGINE=InnoDB;'  --endpoint-url http://127.0.0.1:8080
...
$ aws rds-data execute-statement --database 'test' \
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
    --sql $'INSERT INTO dt  values (:datetime)' --parameters '[{"name":"datetime", "value": {"stringValue":"2021-03-10 22:41:04.123456"}, "typeHint": "DATETIME"}]'  --endpoint-url http://127.0.0.1:8080
...
$ aws rds-data execute-statement --database 'test' \
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
    --sql $'INSERT INTO dt  values (:datetime)' --parameters '[{"name":"datetime", "value": {"stringValue":"2021-03-10 22:41:04.123456"}}]'  --endpoint-url http://127.0.0.1:8080
...
$  aws rds-data execute-statement --database 'test' \                                           
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN --endpoint-url http://127.0.0.1:8080 \
        --sql $'INSERT INTO dt (datetime) VALUES (\'2021-03-10 22:41:04.123456+02\')'
...
$ aws rds-data execute-statement --database 'test' \
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
       --sql $'SELECT * from dt'  --endpoint-url http://127.0.0.1:8080
 {
    "numberOfRecordsUpdated": 0,
    "records": [
        [
            {
                "stringValue": "2021-03-10 22:41:04.0"
            }
        ],
        [
            {
                "stringValue": "2021-03-10 22:41:04.0"
            }
        ],
        [
            {
                "stringValue": "2021-03-10 22:41:04.0"
            }
        ]
    ]
}

[ArsenyYankovsky]

Thanks for trying it. I can still reproduce it.

https://github.com/ArsenyYankovsky/typeorm-aurora-data-api-driver/blob/59905238727e45e322adc353fb64006e2851dde0/test/functional/mysql/simple-queries.mysql-func.test.ts#L229
This is the test I have.

It requires nodejs and yarn to run.

If you want to run it please do the following:

1. Check out the repo
2. Install dependencies

yarn

3. Build the project

yarn build

4. Install forked TypeORM

yarn add typeorm@git://github.com/ArsenyYankovsky/typeorm.git#data-api-prepare-hydrate-packaged

5. Run the actual test

npx jest --testNamePattern=^aurora data api > simple queries should handle date/time types$ --runTestsByPath test/functional/mysql/simple-queries.mysql-func.test.ts

[koxudaxi]

@ArsenyYankovsky
I'm sorry for my late reply.
And, Thank you for the test.

I have released a fixed version as 0.6.7.
The version can pass your date/time tests.

[ArsenyYankovsky]

@koxudaxi Thanks for the fix and for the awesome project. Great job!