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
Working on this same project now. Using the transaction table as an example, this solution wouldn’t work since that field is a TIMESTAMP and contains no time zone information. Did you run into this same issue? Also, were you able to get non-truncated DATETIME/TIMESTAMP values back from the SuiteQL API calls? I have been scouring the internet looking for a way to do this without have to use the TO_CHAR() function on every applicable column. I haven’t found a way to alter the session to set the NLS_TIMESTAMP_FORMAT parameter.
