Hi folks.
I’m working with the google spreadsheets node here, and I think I need some pointers for how best to handle reading timestamps.
For context, I’ve finally found some time to use n8n to automate a manual process I have outlined at the link below, and it might help to skim over that post too, for context.
Anyway, I’ve figured out how to get N8N to talk to google spreadsheet and mailchimp and pull data out, and I’m now working with the spreadsheet that is populated by form submissions. It looks like the final part of automating things isn’t possible with a free slack account, but that’s something I accept now.
So I have a workflow, and it appears to be working, but I’m not sure if I’m doing it right, and it’s working by sheer luck.
Parsing a string like am doing in the example I have shared feels pretty brittle, so I want to sanity check my approach so I can tidy it up and document it properly.
Let’s imagine I have a spreadsheet with the following columns
- Timestamp
- Email Address
- Our weekly newsletter
- City and Country
- Why are you interested in joining
- What’s the main thing you’d like to offer as a part of the community
- Code of Conduct
And some sample data looks like this:
{
"Timestamp": 44474.52290820602,
"Email Address": "[email protected]",
"Our weekly CAT newsletter": "Sure, sign me up",
"City and Country": "Berlin, Germany",
"Why are you interested in joining? ": "I'm just testing this to see if we can still see sign ups",
"What's the main thing you'd like to offer as a part of the community?": "Time, and expertise, but no money, because I am broke.",
"Code of Conduct": "Yes I have read the code of conduct, and agree to follow it."
}
I want to take this information, and every week, run a workflow to fetch all the rows that have a greater timestamp than a given date.
Most of this I understand, but the timestamp looks pretty strange. What is this float representation here? It doesn’t appear to be a unix style millliseconds since the epoch timestamp.
A work around that feels pretty brittle - is this right?
If I change the Value Render option from unformatted to formatted I see the more familiar timestamp (I’ve left out the rest) for my locale:
{
"Timestamp": "10/5/2021 12:32:59",
}
And I can parse this like so, to give me a number I can work with:
bigAssInt = Date.parse("10/5/2021 12:32:59") // returns 1633429979000
But this feels extremely brittle.
What does that weird timestamp float refer to anyway?
I have no idea how we arrive at 44474.52290820602, while I have something working now, it would be nice to understand what’s going on for later debugging.