REST API vs ODBC Timezone Issues
Hey everyone! Pulling data from Netsuite to a data lake implementaton. For performance reasons, we are not using the API to do our initial load, but instead used the ODBC connection. Moving forward, though, we are using the API. It appears that the ODBC connection returns datetimes in GMT, but the API returns the same data in the timesone offset used in NetSuite.
Anyone know off-hand how we can convert the API timezone on the fly? For example:
API Payload:
{
"q": "SELECT * FROM transaction WHERE lastmodifieddate > {ts'2022-04-13 14:00:00′}"
}
This gives me data 7 hours behind what was returned via the ODBC. Ideally, I’d like all date/times returned GMT
Thanks,
Scott
So I confirmed the ODBC does in fact use GMT and the API uses the timezone that is setup in NetSuite. In order to pull via SuiteQL using the API, you need to write your queries using SYS_EXTRACT_UTC function around all your datetime fields. For example:
{"q": "SELECT * FROM transaction WHERE SYS_EXTRACT_UTC(lastmodifieddate) >= {ts'2022-04-13 16:52:04'}"}
Hope this helps someone!
Thanks,
Scott
