Is there SOQL function to convert Date to Unix timestamp in Salesforce SOQL or SOSL?

Question

Hi Salesforce developers,

I am exporting standard Contact fields with Salesforce SOQL. I need to import it to other tool but I need to convert LastModifiedDate, which is ISO datetime string, into Unix timestamp seconds from 1/1/1970.

SELECT Email,LastModifiedDate FROM Contact

    Email                           LastModifiedDate
1   [email protected]          2020-10-27T14:06:12.000Z
2   [email protected]   2020-10-27T14:06:11.000Z

Do you have any ideas how to do that?

I am using iPaaS platform to load SOQL via API (app.tray.io, elastic.io and Salesforce Workbench for debugging issues) so I don't have access to write custom javascript or apex code.

I checked the official Salesforce Date functions, but I don't see a function that I need.

Not working:

SELECT Email,FirstName,LastModifiedDate.getTime(),LastName FROM Contact
SELECT Email,FirstName,DateTime(LastModifiedDate).getTime(),LastName FROM Contact

Thank you for your ideas!

eyescream eyescream · Accepted Answer · 2020-11-12T11:54:15

Can you make a new formula field (type = number) with something like (LastModifiedDate - DATETIMEVALUE('1970-01-01 00:00:00')) * 24 * 60 * 60. And SELECT LastModifiedTimestamp__c... or however you'd call it.

I don't remember if it does seconds or milliseconds, you might have to multiply by 1000. Bit wasteful to have a field just for that but well, what you can do.

Is there SOQL function to convert Date to Unix timestamp in Salesforce SOQL or SOSL?

1 Answers