Turn standard date "DD/MM/YYYY" into UNIX Time

Question

We have a form capture apps from front end that need to pass the value into a UNIX database system.

Since there is no direct integration available, we use Zapier to do this, and it works well except for date conversion, as since we code it in JSON (via Zapier webhook), the UNIX database system muse conform to Unit Date format (e.g. for DOB 1/1/1970 will need to be written as "DOB" : "/Date(-39600000+1100)/",)

The challenge is, how do I convert the standard DD/MM/YYYY into the above format via zapier (since i use it for the integration) so the database can accept it without issue?

Answer 1

I'm presuming the date format you are talking about is the ISO date format. It looks like this - 2018-03-16T00:00:00.000Z.

You might want to add an intermediary step to format the date field received from your Form Trigger.

Method 1 - Using Zapier Formatter App:

1. Add an action after your trigger step and choose the Formatter app. screenshot
2. Select the Date/Time option screenshot
3. Choose Format under Transform. In the input field, select the date field from the trigger step.
4. Setup the to format, from format, and timezone as required. screenshot
5. Use this field in your next Webhook action.

Zapier has an article on formatting dates here.

Method 2 - Using Code app by Zapier: In case you need more control, you can use the code app by Zapier.

1. Choose the Code app, select "Run Javascript".
2. In input data set the date field from your trigger app to a property named 'iDate'. screenshot
3. In the code section, copy and paste the code below.
4. In the last line you can change the strJSON text to any of the other formats listed above it.
5. Use the date field output from the code step in your Webhook action.

var yourDate = inputData.iDate;
var dArr = yourDate.split('/');
var d = Date.parse(`${dArr[2]}-${dArr[1]}-${dArr[0]}`);

//String representation of the Date for JSON
var strJSON = new Date(d).toJSON();
console.log('JSON String Representation: ', strJSON);

//ISO Date Format
var iso = new Date(d).toISOString();
console.log('ISO: ', iso);

//Date String
var str = new Date(d).toDateString();
console.log('Date String: ', str);

//Date String with time
var strTstamp = new Date(d).toString();
console.log('Timestamp String: ', strTstamp);

var output = {date: strJSON};

Update:

You can use the below code in the Code app on Zapier (do the setup as instructed above). This will output the time in the format you specified in your comment -39600000+1100.

var yourDate = inputData.iDate;
var dArr = yourDate.split('/');

//11*60*60*1000 for +11:00 Timezone. It is converting 11 hours into milliseconds.
//If your timezone is +05:30, 5*60*60*1000+(30*60*1000)
var unixTime = new Date(`${dArr[2]}-${dArr[1]}-${dArr[0]} 00:00:00 UTC`).getTime() - (11*60*60*1000);

//If your timezone is not +11:00, swap it below.
var unixWithTZ = `${unixTime}+1100`

console.log('UnixTime: ', unixWithTZ);
console.log('UnixWithTZ: ', unixWithTZ);

var output = {date: unixWithTZ};

Read more about Unix Time here.