Redwood: Error: GraphQL error: DateTime cannot represent an invalid Unix timestamp

[ryancwalsh]

RedwoodJs seems to store timestamps as 13-digit integers (e.g. 1588608976714), which https://www.epochconverter.com/ says presumably is milliseconds rather than seconds and is equal to Monday, May 4, 2020 4:16:16.714 PM GMT because as seconds would be waaay in the future on Fri Jan 6, 52311 6:18p EST (America/New_York)

But if I change from using db.principle.findMany() to using a raw query:

db.raw`SELECT p.id, p.title, p.description, p.createdAt, p.modifiedAt
    FROM principle p
   WHERE EXISTS (SELECT NULL
                   FROM _PrincipleToTag pt
                  WHERE pt.B IN (${tagIds.join(',')})
                    AND pt.A = p.id
               GROUP BY pt.A
                 HAVING COUNT(DISTINCT pt.B) = ${tagIds.length})`

I get this error:

Error: GraphQL error: DateTime cannot represent an invalid Unix timestamp 1588538742876

Am I doing something wrong?

(Really I'd prefer not to use "raw" at all but haven't figured out how: https://stackoverflow.com/questions/61583460/prisma-2-query-to-return-records-only-that-are-associated-with-all-of-the-provid/)

Thanks!

P.S. I'm so new to RedwoodJs, GraphQL, Apollo, and Prisma that I don't even know if I'm reporting this problem in the correct place. Thanks for all of your impressive work. :-)

[cannikin]

Can you try a console.log of the data returned by both findMany and your raw version and see what the data looks like? My guess is that Prisma is coercing the value in findMany but not in raw. If that's the case you'll need to take it up with them! In the meantime, you could loop through the results from raw and coerce them manually.

GraphQL is complaining that it's getting the wrong datatype returned by the resolver (the code you write in the service), but it's not a GraphQL issue.

[ryancwalsh]

@cannikin raw shows results that have values like createdAt: 1588538742876, and findMany's results include values like createdAt: 2020-05-03T20:45:42.876Z.

I reported to https://github.com/prisma/prisma-client-js/issues/682 (hopefully that's the right place).

I couldn't find any documentation about how to "coerce" values and am not sure where I'd do it. I'm still trying to wrap my head around the lines of responsibility of RedwoodJs, GraphQL, Apollo, and Prisma (it's not clear to me what is what).

Thanks, Rob!

[cannikin]

@ryancwalsh thanks for trying that it! It's interesting to see where Prisma does and doesn't get involved in massaging your data for you.

"Coerce" really just means turning something from one type to another (forgive me if you already know all of this!). So if you have the number "123" but it's a string and you want it to be a number, you can do parseInt("123") and that turns it into a Javascript Number. In your case the createdAt is coming out of the database as a Number but you want it to be a Datetime. It's actually pretty easy to turn an epoch into a real Javascript Date thing. You have to do two steps which is kind of annoying, but works:

const createdAt = 1588538742876
const date = new Date(0)
date.setUTCSeconds(createdAt / 1000)

(Note the / 1000 to get it from milliseconds to seconds)

That turns it into a Date, but when you look at the value (like in console.log(date)) it'll be something like Sun May 03 2020 13:45:42 GMT-0700 (Pacific Daylight Time) which is not the same as what Prisma is returning with findMany(). The format Prisma returns is the ISO 8601 standard, which is what databases tend to use. To go from a Javascript date to ISO date, you can do:

date.toISOString()

Which returns "2020-05-03T20:45:42.000Z" which is exactly what we want.

SO, how to do that in Redwood so that it works for GraphQL? Have you read this page of the tutorial: https://redwoodjs.com/tutorial/side-quest-how-redwood-works-with-data It tries to explain how data moves and the lines between GraphQL and Prisma. Prisma is purely for talking to the database. GraphQL is an API between the browser and the serverless functions. Those serverless functions use Prisma to get data from the database and return it to GraphQL for sending back down to the browser.

So we need to coerce those values before they get to GraphQL, which means all the transformation needs to happen inside the service file. All GraphQL cares about is that the object it gets back from the function in the service matches the format you defined for the type in the SDL file. In your case I'm guessing that's something like:

type Principle
  id: Int!
  title: String!
  description: String!
  createdAt: DateTime!
  modifiedAt: DateTime!
}

So that means the object returned from the service function needs to look like:

[
  {
    id: 123,
    title: "Hello, world",
    description: "This is a hello, world example!",
    createdAt: "2020-05-03T20:45:42.000Z",
    modifiedAt: "2020-05-03T20:45:42.000Z"
  }
]

Technically createdAt and modifiedAt are both just strings, but apparently as long as they're in the right format GraphQL will consider them proper DateTime objects.

So all you need to do it take the data returned from Prisma and turn it into that expected format 👆. I'll take a stab at what your service function might look like, but you may have to make some modifications. But this should get you close!

// api/src/services/principles/principles.js

import { db } from 'src/lib/db'

export const principles = async () => {
  const records = await db.raw`SELECT p.id, p.title, p.description, p.createdAt, p.modifiedAt
    FROM principle p
   WHERE EXISTS (SELECT NULL
                   FROM _PrincipleToTag pt
                  WHERE pt.B IN (${tagIds.join(',')})
                    AND pt.A = p.id
               GROUP BY pt.A
                 HAVING COUNT(DISTINCT pt.B) = ${tagIds.length})`

  return records.map(principle => {
    const createdAtDate = new Date(0)
    createdAtDate.setUTCSeconds(principle.createdAt / 1000)
    const modifiedAtDate = new Date(0)
    modifiedAtDate.setUTCSeconds(principle.modifiedAt / 1000)

    return Object.assign(principle, { 
      createdAt: createdAtDate.toISOString(), 
      modifiedAt: modifiedAtDate.toISOString() 
    })
  })
}

Note that I turned this into an async/await function so that you can wait for Prisma to get you the data and then you can play with it. This function should now take each principle returned from your query and override the createdAt and modifiedAt values as needed for GraphQL.

I hope that works, let me know!

[ryancwalsh]

@cannikin WOW! What a generous answer. Thank you so much. 👏

That last part was what I didn't understand, and yeah, this seems to solve that problem. Very cool.

export const principles = async ({  tagIds }) => {
  const records = await db.raw`SELECT p.id, p.title, p.description, p.createdAt, p.modifiedAt
    FROM principle p
   WHERE EXISTS (SELECT NULL
                   FROM _PrincipleToTag pt
                  WHERE pt.B IN (${tagIds.join(',')})
                    AND pt.A = p.id
               GROUP BY pt.A
                 HAVING COUNT(DISTINCT pt.B) = ${tagIds.length})`

  return records.map((principle) => {
    const createdAtDate = new Date(0)
    createdAtDate.setUTCSeconds(principle.createdAt / 1000)
    const modifiedAtDate = new Date(0)
    modifiedAtDate.setUTCSeconds(principle.modifiedAt / 1000)
    const momentsAsIso = {
      createdAt: createdAtDate.toISOString(),
      modifiedAt: modifiedAtDate.toISOString(),
    }
    console.log('momentsAsIso', momentsAsIso)
    return Object.assign(principle, momentsAsIso)
  })
}

I'm closing this issue since it sounds like it's not a RedwoodJs issue.

(We'll see what happens at https://github.com/prisma/prisma-client-js/issues/682 and the other issue affecting what I'm trying to do related to raw at https://github.com/prisma/prisma/issues/1929#issuecomment-623712981 and waiting for more supported functionality so that I don't need to use raw queries: https://stackoverflow.com/a/61590943/470749 and https://stackoverflow.com/q/61583460/470749 )

Thanks again!

[cannikin]

You're welcome! You were asking for help so nicely that I wanted to give a nice, thorough answer. Glad it worked!