What's the best way to transfer Firestore Timestamp data to Postgres in Cloud SQL?

Hey folks, I’m stuck trying to move some data from Firestore to Postgres in Cloud SQL. My Firestore collection has a Timestamp field, but I can’t figure out how to properly insert it into Postgres.

Here’s what I’ve got so far:

function copyDataToPostgres(snapshot) {
  const userInfo = snapshot.data();
  const timestampField = userInfo.lastLogin;

  // This part isn't working
  return dbConnection.query(`
    INSERT INTO user_table (id, username, last_login)
    VALUES ('${snapshot.id}', '${userInfo.username}', ${timestampField})
  `);
}

When I run this, I get a syntax error near ‘[’. I think it’s because the Timestamp is coming through as [object Object].

Any ideas on how to convert the Firestore Timestamp to something Postgres can understand? I’m pretty new to both these databases, so any help would be awesome. Thanks!

hey there! have you tried converting the timestamp to a date string first? maybe something like:

const pgTimestamp = timestampField.toDate().toISOString();

then use that in your query? just a thought! what other approaches have you considered? curious to hear how it goes!

yo dancingbutterfly! i’ve dealt with this before. try using the toMillis() method on your timestamp, then convert to a postgres timestamp. something like:

${timestampField.toMillis() / 1000}

this gives you unix time in seconds. postgres can handle that easily. lmk if it works!

To properly transfer Firestore Timestamp data to Postgres, you’ll need to convert it to a format Postgres can understand. I recommend using the toDate() method on the Firestore Timestamp, then formatting it as an ISO string. Here’s how you can modify your code:

function copyDataToPostgres(snapshot) {
  const userInfo = snapshot.data();
  const timestampField = userInfo.lastLogin.toDate().toISOString();

  return dbConnection.query(`
    INSERT INTO user_table (id, username, last_login)
    VALUES ('${snapshot.id}', '${userInfo.username}', '${timestampField}')
  `);
}

This approach should resolve the syntax error you’re encountering. Remember to use parameterized queries in production to prevent SQL injection vulnerabilities.