AWSDateTime scalar is not currently supported by RDS datetime type

[MaximumCrash]

Describe the bug
This is more of an issue with AppSync and RDS, I've already put in a ticket with them and the feature/bug ticket has been put in with the Amazon team. But I thought it'd be good to alert this community on this particular issue.

When pulling in an RDS datasource and a table has a column of a datetime type, the graphql-transformer applies the AppSync scalar AWSDateTime (as expected). The issue however is that the format of AWSDateTime (YYYY-MM-DDThh:mm:ss.sssZ) conflicts with the expected MySQL format of Datetime types (YYYY-MM-DD HH:MM:SS.sss).

My temporary solution is to change all AWSDateTime scalars to strings and use the MySQL datetime format.

To Reproduce
Steps to reproduce the behavior:

1. Create an RDS cluster, database, and simple table with a column that has a datetime type.
2. Create a javascript project with amplify.
3. Add a RDS datasource (MySQL 5.6 Aurora Serverless)
4. See the graphql.schema with AWSDatetime scalars (as expected)
5. Try running a mutation to create a record in the RDS datasource using the expected AWSDateTime format.
6. See the error returned from RDS

Expected behavior
My hope was that RDS datetimes would support the AWSDateTime format out the box, but this is an internal conflict between services.

[manchicken]

The JavaScript which is generated also attempts to push dates up in a format different from what AWSDateTime expects: "2019-08-17 05:49:55"

Omitting the field doesn't help, either, as the resolver seems to choke on the existing value in the table as well.

[djheru]

I've also run into this bug, which is present in the example Marketplace schema in the amplify add-graphql-datasource section of the docs 😞

[lspellman]

I have encountered this issue as well. Existing table data, generated through regular SQL calls, produces a DateTime value in table like this: 2019-04-17-19.00.00 -0500. When returned via AppSync resolver, the cloudwatch log shows the ResponseMapping transformedTemplate as: 2019-04-18 00:00:00.0 which then results in a null value in the response.

I guess I'm more concerned that the query is returning a "rounded up" date value before it even attempts to transform it. If the format were successfully transformed, it would still be "wrong".

[mdepascale]

Our temporary solution:
BEFORE
$util.qr($context.args.input.put("created_at", $util.time.nowFormatted("yyyy-MM-dd HH:mm:ss")))

AFTER

#set( $output = $utils.rds.toJsonObject($ctx.result) )
#if( $output.get("created_at") )
     #set( $createdAt = $output.get("created_at").replace(" ", "T"))
     $util.qr($output.put("created_at", "${createdAt}.000Z"))
#end

Hope they will solve this issue

[pcvvelzen]

Our temporary solution:
BEFORE
$util.qr($context.args.input.put("created_at", $util.time.nowFormatted("yyyy-MM-dd HH:mm:ss")))

AFTER

#set( $output = $utils.rds.toJsonObject($ctx.result) )
#if( $output.get("created_at") )
     #set( $createdAt = $output.get("created_at").replace(" ", "T"))
     $util.qr($output.put("created_at", "${createdAt}.000Z"))
#end

Hope they will solve this issue

For the less experienced Amplify developers, in which file could I configure this (what seems to be a customer resolver)?

[mdepascale]

For the less experienced Amplify developers, in which file could I configure this (what seems to be a customer resolver)?

Yeah you should copy your resolver.req.vtl and resolver.res.vtl files from build to your resolver folder, then edit them with whatever you need them to do
They will then become custom resolvers

[pcvvelzen]

Yeah you should copy your resolver.req.vtl and resolver.res.vtl files from build to your resolver folder, then edit them with whatever you need them to do
They will then become custom resolvers

Just to have the solution clear to me and others bumping into this through online search. I would like to clarify how to implement the solution based on this example schema based, generated from MySQL table:

CREATE TABLE IF NOT EXISTS `page` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `createdAt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `title` VARCHAR(60) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

It appears that using RDS as datasource with most recent Amplify-CLI the resolvers are already copied to amplify/backend/api/<API_NAME>/resolvers after generating schema.

From your example it is not exactly clear to me how to implement the fix, it would be great if you could provide example to assist us on how to the default generated .res.vtl files from Amplify should be changed. As it seems to rely on $utils.toJson(.. to return the output results.

• amplify/backend/api/<API_NAME>/resolvers/Query.listPages.res.vtl, content:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0])

• amplify/backend/api/<API_NAME>/resolvers/Query.getPage.res.vtl, content:

$utils.toJson($output[0])

[mdepascale]

Alright assuming your query is
getPage(id: Int!) : Page

you will need to edit

• amplify/backend/api/<API_NAME>/resolvers/Query.putPage.res.vtl with:
  $util.qr($context.args.input.put("created_at", $util.time.nowFormatted("yyyy-MM-dd HH:mm:ss"))), just add this line before making the PUT request
• amplify/backend/api/<API_NAME>/resolvers/Query.getPage.req.vtl with:

#set( $output = $utils.rds.toJsonObject($ctx.result) )
#if( $output.isEmpty() )
  $util.error("Invalid response from RDS DataSource. See info for the full response.", "InvalidResponse", {}, $output)
#end
#set( $output = $output[0] )
#if( $output.isEmpty() )
  #return
#end
#if( $output.get("created_at") )
     #set( $createdAt = $output.get("created_at").replace(" ", "T"))
     $util.qr($output.put("created_at", "${createdAt}.000Z"))
#end
$utils.toJson($output[0])

it should work, I will double check it tomorrow

[georgeleeventures]

Is there any update on this issue? I highly doubt the graphql RDS datasource part of Amplify is production-ready. It seems like we'd have to make modifications to all of the resolver templates... Not only do I have this issue with AWSDateTime scalar, but I also have issues with JSON being returned improperly from the resolver too.

[jaubrey-ebar]

I confirm this still exists. It's a pretty ridiculous oversight in the Amplify API IMO.

Error when trying to send JavaScript Date -> GraphQL AWSDateTime -> RDS:
RDSHttp:{"message":"Database error code: 1292. Message: Data truncation: Incorrect datetime value: '2021-10-21T07:21:25.803Z' for column 'modified' at row 1"

MySql / JavaScript Workaround:

1. param.modified = dateToGraphQL(Date()) -> GraphQL Mutation w/ String param -> StoredProc(IN modified VARCHAR(27)) -> UPDATE MyTable SET modified = fromIso8601(modified)
2. SELECT toIso8601(DateTime()) -> GraphQL Query AWSDateTime -> Date

JavaScript Helper:

export function dateToGraphQL(input) {
    return input === null ? null : input.toISOString(); // ISO 8601 format
}

MySql Helpers:

DELIMITER //

DROP FUNCTION IF EXISTS toIso8601//

CREATE FUNCTION toIso8601(input DATETIME(6)) RETURNS CHAR(27) DETERMINISTIC
BEGIN
    --                123456789012345678901234567
    -- result string: YYYY-MM-DDThh:mm:ss.ffffffZ
    RETURN DATE_FORMAT(input,"%Y-%m-%dT%H:%i:%s.%fZ");
END//

DROP FUNCTION IF EXISTS fromIso8601//

CREATE FUNCTION fromIso8601(input VARCHAR(27)) RETURNS DATETIME(6) DETERMINISTIC
BEGIN
    --               123456789012345678901234567
    -- input string: YYYY-MM-DDThh:mm:ss.ffffffZ
    RETURN STR_TO_DATE(input,'%Y-%m-%dT%H:%i:%s.%fZ');
END//

DELIMITER ;

[renebrandel]

Hi - Thanks for raising this. We are looking to address this as an immediate priority via an upcoming release to GraphQL Transformer v2.

I would love to get your thoughts and feedback on the RFC with the details: #815

[chrisbonifacio]

Closing this as the API generated by AmplifyGraphqlApi seems to have addressed this issue.

I created an API following this guide: https://docs.amplify.aws/react/build-a-backend/graphqlapi/connect-api-to-existing-database/

Here is an example of a mutation using the same datetime value that reproduced the following error: RDSHttp:{"message":"Database error code: 1292. Message: Data truncation: Incorrect datetime value: '2021-10-21T07:21:25.803Z' for column 'modified' at row 1"

GraphQL Schema:

type Post @refersTo(name: "posts") @model @auth(rules: [{ allow: public }]) {
  id: String! @primaryKey
  title: String
  content: String!
  published: Int @default(value: "0")
  blogId: String! @refersTo(name: "blog_id")
  publishedDate: AWSDateTime @refersTo(name: "published_date")
}

MySQL Schema

create table posts
(
    id             varchar(40)          not null
        primary key,
    title          varchar(255)         null,
    content        varchar(255)         not null,
    published      tinyint(1) default 0 not null,
    blog_id        varchar(255)         not null,
    published_date datetime             null
);