RE: 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

txaggie00 Rookie Asked on April 13, 2022 in SuiteScript.
Add Comment
1 Answers

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

Rookie Answered on April 14, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.