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

Data and Databases
1

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!

2

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!

3

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!

4

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.