This article is relevant if you are working with NetSuite SuiteScript and seeking to update datetime fields.
Background
In recent work on the Bitcoin Transaction Coordinator, we are pulling in Bitcoin prices in NetSuite from from various exchanges around the globe. It’s important that we maintain up to the second prices in this service. Accordingly, using NetSuite’s datetime field type allows for granular control and precision when working with date and time information.
Our team is based in West Coast of the United States and hence, we are in the Pacific Time Zone. NetSuite is also headquartered in San Mateo California also on the Pacific TimZone. Due to this, our development efforts produced a blindness to some of the nuances when working with NetSuite and Datetime data types.
Before going further, I must give thanks to Joe Son who has become a friend and colleague in the NetSuite space. He warmed me a number of times about TimeZone considerations on the NetSuite platform. In particular, his article here helped the team better understand what is happening. This prompted me to write this article to offer a different perspective to Joe’s.
NetSuite Server Side Dates
At the time of this writing, NetSuite has two data centers in the United States. The system clocks in those data centers are set to the Pacific Timezone. It’s probably safe to assume that NetSuite set this clock timezone back in the 1990s when the service was first launched in California. The meaning of this is that when you make a simple JavaScript date function call, such as : set d = new Date();, the time you will get back is in the Pacific Timezone.
When ever you work with NetSuite datetime types, it is important to work with the nlapiDateToString and nlapiStringToDate functions to and from JavaScript native dates and NetSuite dates. The typical way you update a datetime field in NetSuite is to call:
record.setFieldValue('customfield', nlapiDateToString(new Date(), 'datetimetz')).
Here is where it gets confusing.
- Server side, scripts are always running under a particular user context.
- Scripts running as Administrator, such as Scheduled Scripts, are effectively in the Company timezone.
- The user preferences (Home, Set Preferences, Timezone) govern the timezone that the script is running; except in the case of Administrator scripts which are in the Company Timezone (Setup, Company, Company Preferences).
- When you perform a record.setFieldValue(‘customfield’, nlapiDateToString(new Date(), ‘datetimetz’) ) update, the system effectively stamps the information with timezone based on the above rules. Example, if your NetSuite user account is set to indicate you are in the Eastern Timezone, then the update will deem the date to be in the Eastern Timezone.
To handle this, you simply need to use NetSuite’s new TimeZone API to specify that the datetime you received is indeed in the Pacific Timezone. Modify:
record.setFieldValue('customfield', nlapiDateToString(new Date(), 'datetimetz'))
function call to now become
record.setDateTimeValue('customfield', nlapiDateToString(new Date(), 'datetimetz'), 5)
where 5 is the Olson value for the Pacific Timezone.
Once you use this update method, then the actual time stored in the database will be correctly referenced. When users view the datatime data, it will be properly offset by their specific Timezone setting.
Get Assistance
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
Nice article, Marty!
I would like to point out that this API is also aware of the daylight savings, so it keeps the correct reference to that specific date, whether it was under a daylight savings scenario or not.
For example, let’s say we have two custom records that store a date/time field, with the following values:
Record 1 = ‘1/2/2014 18:00:00’
Record 2 = ‘7/15/2014 18:00:00’
The company and user are set to the Brasilia time zone (GTM-3).
Now, we need to grab the date and time of those records and transform them to a JavaScript Date Object.
Two important things to note:
1) At Jan 2nd (first date), Brasilia was under daylight savings, while Pacific was not;
2) At Jul 15th (second date), Brasilia was not under daylight savings and Pacific was.
If we run this code on the Script Debugger:
var record1 = nlapiLoadRecord(‘customrecord_test’, 1);
var record2 = nlapiLoadRecord(‘customrecord_test’, 2);
var date1 = record1.getDateTimeValue(‘custrecord_test_date’, 5);
var date2 = record2.getDateTimeValue(‘custrecord_test_date’, 5);
var dateObject1 = nlapiStringToDate(date1, ‘datetimetz’);
var dateObject2 = nlapiStringToDate(date2, ‘datetimetz’);
Event though both dates are set to the same time (18:00), you will notice that date1 and date2 variables are two hours apart, which is the result of the daylight savings in Brasilia and Pacific. Now, when we convert those strings to a Date object (dateObject1 and dateObject2 variables), you will notice the timezone reference being kept:
dateObject1 = {date} Thu Jan 02 2014 12:00:00 GMT-0800 (PST)
dateObject2 = {date} Tue Jul 15 2014 14:00:00 GMT-0700 (PDT)
It is very important to be aware of this, specially when time is critical to your application or when you make decisions based on date and time comparisons.
Jaime.
Hello Jaime,
Thank you for the clarification. Your example emphasizes the need to use these timezone APIs when working with server side JavaScript date.
Marty
Marty,
What about when dealing with time alone in Netsuite? This is very helpful for date objects, but when dealing with tracking time (CRM fields) with Start and End times, there seems to be no way to get the start time for a particular time zone…
For example, we create call records (integrated with our phone system) when a call is placed from the Customer record. However, when we attempt to set the start time of the call (from the Eastern time zone, GMT -5) we can only retrieve Pacific times…
Elie,
Are you setting those times server side? I ask because we built a NetSuite phone system integration for ShoreTel but we used client side technology. This article is all server side. Say more and let’s see if we can solve it.
Marty
Marty,
I am using the ODBC connector and I see that the timestamps for last modified are saved in GMT+2. While the Netsuite application servers are in Pacific time, is it safe to assume the database is in eastern european time?
Hi Tony,
Check your user preferences for the role you are logged in. Is that giving you the timezone you are seeing?
Marty
I think I’m setting my field correctly, following the logic above, but I’m doing something wrong when getting it out.
https://stackoverflow.com/questions/42144298/how-to-get-datetime-value-from-a-custom-field-correctly
Hey,
Can we get an update on this article using a more current version of Suitescript?
Thanks!
I need to test this. But it should be something like this:
// Format the current date and time to include the correct time zone (Pacific Time)
var formattedDate = format.format({value: new Date(),
type: format.Type.DATETIMETZ,
timezone: format.Timezone.AMERICA_LOS_ANGELES // Pacific Time (PST/PDT)
});
Marty