Unable to serialise AWSDate from RDS in AppSync response mapping template

Question

I'm building an AppSync project using serverless Aurora as my db, and stumbled across this strange error:

"Can't serialize value (/getUsers/created_at) : Unable to serialize `2019-09-28 07:36:13` as a valid DateTime Object."

This happens when I get a User object which looks like this:

type Users {
  id: String!
  name: String!
  description: String
  created_at: AWSDateTime
  updated_at: AWSDateTime
  logged_in: AWSDateTime
}

The error seems to be happening because $utils.rds.toJsonObject($ctx.result)[0][0] can't parse an AWSDateTime. Which makes anything with a date impossible to serve from the database.

If I simply select the object without dates ["SELECT id,name,description FROM Users WHERE id='$ctx.args.id'"] it works fine.

So how should dates be handled in AWS AppSync and Aurora? I have been unable to find any example or reference to handling dates in the documentation. :(

Answer 1

I accidentally found the answer in a repo connected to this question Use AppSync and Amazon RDS with serverless-graphql

It turns out, like user dev1702 discovered, that $utils.rds.toJsonObject($ctx.result)[0][0] cannot parse a RDS Timestampt INTO an GraphQl AWSDate format.

So simply changing the graphql schema type from:

created_at: AWSDateTime

to:

created_at: String

solves this issue.

A Note: If you are facing this error after changing your scheme column type to AWSDateTime, just visit the DynamoDB console and update records that do not in the format of ISO 8601.

Answer 2

You can SELECT to_char(created_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') FROM Users which will work for AWSDateTime.

Another one is SELECT created_at::date FROM Users which will work for AWSDate.

Answer 3

you need to enter your date time as => 2022-08-01T10:10:42-05:00

Answer 4

If you want to output from SQL in a format that AWSDateTime supports and want to support milliseconds you can do:

SELECT to_char(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') FROM Users