Amplify-cli: 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

[peterv1988]

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.vtland 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

[peterv1988]

Yeah you should copy your resolver.req.vtland 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