Set Date String to Midnight UTC

Questions
,
1

I’m updating our CRM, Hubspot, with a date string.

Hubspot requires all dates to be set at midnight UTC in Unix with millisecond format. My JavaScript isn’t great, but using setUTChours in an expression has worked in other workflows. I’m not sure what the expression looks like in this case, though, or which node to use. The two fields that need to be converted are license_start_date and license_end_date. Would a Set node with an expression work, or do we need a Function Item node following the date/time nodes?

Please share the workflow

Share the output returned by the last node

The execution fails on the Update Hubspot node with this error message:

{“message”:“400 - {"validationResults":[{"isValid":false,"message":"15000 is at 0:0:15.0 UTC, not midnight!","error":"INVALID_DATE","name":"date_subscription_start"},{"isValid":false,"message":"15000 is at 0:0:15.0 UTC, not midnight!","error":"INVALID_DATE","name":"date_subscription_end"}],"status":"error","message":"Property values were not valid","correlationId":"e0862009-ea4a-4076-b457-128a57fe5363"}”,“name”:“Error”,“stack”:"Error: Request failed with status code 400\n at createError

Information on your n8n setup

  • n8n version: 0.161.1
  • Database you’re using (default: SQLite): default
  • Running n8n with the execution process [own(default), main]: default
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: Docker
2

Hey @kcawley,

What is the format of the date coming from MySQL? You might be able to just do it as part of the query, A couple of other options would be to try setUTCHouse() in a an expression as you mentioned or maybe just cheat and do a slice to get the date and just add the 00:00 to the end.

I would probably put the expression in the Date and Time node so it does the change just before conversion, We do use the Luxon library for dates now though so there could be a way to do it without the 2 date and time nodes :thinking:

If you can pop over an example of the dates as it comes from MySQL I will have a quick play.

3

Hi @Jon,

I fixed the issue in the MySQL query late yesterday, but it would still be great to understand how to format using n8n for future workflows.

In MySQL, the date fields are actually stored as strings in VARCHAR format. Here are some examples:

Sat May 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
Mon Aug 14 2023 00:00:00 GMT-0400 (Eastern Daylight Time)
Wed Jun 30 2021 00:00:00 GMT-0400 (Eastern Daylight Time)

If anyone runs into a similar issue, here is how I corrected in the query:

UNIX_TIMESTAMP(DATE_FORMAT(rl.dte_license_start,’%Y-%m-%d 00:00:00’)) license_start_date

1 Like
4

Hey @kcawley,

The quickest option would be 2 Date and time nodes like below, The first to just get the date and the second to convert that to a Unix timestamp.

5

Thanks @Jon, appreciate you taking the time!

1 Like
6

Hey @kcawley,

No problem at all :+1:

7

Thank you for this solution. I was having the same issue but for some reason, the updated format doesn’t go into the next. I can see the update in the Date & Time node but the remainder nodes convert back to YYYY-MM-DD.

8

Hey @TDean,

It looks like htere is a chunk of your workflow missing as the sample doesn’t get past the function node. If you don’t put those 5 nodes into the one item list and have 5 item list nodes to handle it does it work?

9

Thanks for your quick response! Sorry about that… I posted the full workflow below. No, it doesn’t seem to work with 5 item list nodes.

10

Do you happen to have the output of that HTTP request with anything sensative removed?

11

The output is (the last 5 and birthdate are dates):

{
“properties”:{
“firstname”: “”,
“lastname”: “”,
“user_id”: “”,
“email”: “”,
“address”: “”,
“county”: “”,
“city”: “”,
“zip”: “”,
“member_number”: “”,
“roles”: “”,
“expires_on”: “”,
“background_check_on”: “”,
“ypt_completed_on”: “”,
“certified_on”: “”,
“updated_at”: “”
}}

12

What is the format of the dates?

13

All are YYYY-MM-DD except ‘updated_at’, which is YYYY-MM-DD HH:MM:SS.