https://netsuiteprofessionals.com logo
Join Slack
Powered by
I have this query to find the unique employees who...
# suiteql
r
I have this query to find the unique employees who are allocated for today, what is happening is some of the employees whose allocation is ending today are not coming in this list. There is some issue with the time zones. Our suitescript runs in PST time zone while the resource allocations are saved where user preferences are in IST time zone, I want the result in IST time zone only as well. How can I handle timezone in the queries
Copy code
SELECT 
  resourceallocation.allocationResource 
FROM 
  resourceallocation as resourceallocation 
WHERE 
  resourceallocation.startDate <= SYSDATE + (750 / 1440) 
  AND resourceallocation.endDate >= SYSDATE + (750 / 1440) 
GROUP BY 
  resourceallocation.allocationResource
s
When dealign with multiple timezones, the first thing to ask is, is there even a timezone issue here? So, focusing on the second where clause, 
endDate >= SYSDATE + (750 / 1440)
, what is the intention with that statement? You are already excluding any endDate that not 12.5 or more hours from the current date/time (SYSDATE returns a date/time value, not a Date), regardless of timezone. Is that what you intend? What is the business logic you are trying to achieve? In other words, 
SYSDATE + (750 / 1440)
is always going to be 12.5 hours from now, regardless of timezone (timezone doesn’t matter here)
And actually, I don’t think it even works as intended. 
(750 / 1440)
is going to be an integer division, and will evaluate to 0. If you want it to work as expected, try 
SYSDATE + (12.5 / 24.0)
instead
that will force it to perform floating point division, giving the expected result
r
Users who create allocation have IST timezone preference in personal preference. Our suitescripts run in PST timezone. So before 12:30 PM IST sysdate for today 04/26/2023. After 12:30 PM IST sysdate for today is 04/27/2023. There is no specific time when the server side script will get triggered. Basically in this I want to include all the allocations whose startdate was on or before today and end date was on or after today.
s
No matter what timezone this runs in, SYDATE + X is always going to be some date/time in the future, unless X is negative. SYSDATE is not going to give you the start of day date/time in any timezone, it give the current time (right now)
r
In saved search filters I used to convert the new Date to IST time zone date.
Copy code
var today = new Date();
            var indiaToday = format.format({
                value: today,
                type: format.Type.DATETIME,
                timezone: format.Timezone.ASIA_CALCUTTA
            });
            var finalDate = String(indiaToday).split("/")
Then take the date potion from the string and use it in our filters. I was thinking will have to do siilar in SQL as well, I assumed
Can you please provide a solution to this in SQL?
s
SYSDATE runs on the Database server, while new Date() runs on the application server
r
in our case both will be set to PST timezone right?
s
and in saved searches it was necessary to pass in Date filters as a string, so yes, timezones are an issue
my poitn is, it doesn’t matter
you aren’t passing a value from the script to the database server. SYSDATE is generated by the database, in whatever timezone the database is using.
but really, the timezone doesn’t matter, unless endDate is a Date only field, and not a date/time
r
its the standard netsuite field, I believe it stores the time as well, just doenst display it
s
Try running this, and then compare the results for endDate, and the various versions of SYSDATE (t1, t2, t3): SELECT allocationResource, startDate, endDate, SYSDATE AS t1, SYSDATE + (750 / 1440) AS t2, SYSDATE + (12.5 / 24.0) AS t3 FROM resourceallocation WHERE resourceallocation.startDate <= SYSDATE + 1 AND resourceallocation.endDate >= SYSDATE - 1 GROUP BY resourceallocation.allocationResource
the point is to confirm what each of those is returning back to you, and make sure it’s what you expect it to be
r
I will check and confirm back, what I need is actually I am getting, want to understand properly, will run the script before 12:30 PM IST and post 12:30 PM IST to understand. Will create the scenarios in the mean while.
s
it doesnt matter when the script runs
what matters are the dates in the query, the script has nothing to do with the query, it’s all happening in the database server
notice that, in the query, you aren’t passing in any values from the script
Maybe this example will help illustrate it. I am in the America/New_York timezone, where it is currently 11:40 AM EST The current time in Asia/Kolkata is 9:10 PM IST Running a query with SYSDATE in NetSuite right now, I get the result 
2023-04-27 08:40:00.0
however, all of those times are the same time, they mean the same thing
adding 12.5 hours to SYSDATE will give you the time equivalent to 9:40 AM tomorrow in IST
which also happens to be 
2023-04-27 21:10:00.0
in PST
but, all date/times in Netsuite ill be evaluated in the the PST timezone too,no conversion is needed. Date only fields are tricky, though, and do need special care.
r
Let me wrap my head around it, not focused will check shortly and thankyou for the input
Copy code
Allocation end date = 04/26/2023 00:00 IST = 04/25/2023 11:30 PST.
Case 1:
sysdate  = 04/27/2023 00:00 PST.
case 2:
sysdate  = 04/27/2023 23:30 PST
Copy code
Allocation end date  = 04/27/2023 00:00 IST = 04/26/2023 11:30.
case 3:
sysdate  = 04/27/2023 00:00 PST.
Case 4:
sysdate  = 04/27/2023 23:30 PST
Copy code
Allocation end date = 04/28/2023 00:00 IST = 04/27/2023 11:30.
case 5:
sysdate  = 04/27/2023 00:00 PST.
Case 6:
sysdate  = 04/27/2023 23:30 PST
Expected output, Case 3,4,5,6 should pass Filter (allocation end date on or after today)
Copy code
SQL:
enddate >= sysdate ( only case 5 passes and 3,4,6 fails)
enddate >= sysdate-1 (only case  3,5,6 passes and 4 fails)
After thinking it out, what you suggested this is what I am thinking. Can you clarify. Still need to test it out.
s
Without checking the cases above, I assume that you are correct. Now, you need to run the query with actual records and actual endDate values to verify that your assumption hold true. Ideally, set up test records in a sandbox so you can confirm that, given the values above, you get the results expected.
r
What can I do such that all test case passes ?
s
do you want all of the test cases to pass? I am not clear. in your hypothetical examples above, I assumed you had worked out what you wanted the results to be
but yes, to repeat again, you’ll never get the start of day time, in either PST or IST, by adding or subtracting a constant value from SYSDATE
again, you have to be able to think through your business logic first, so that you can translate it into code or a query
and, I cannot stress this enough, run the queries and log/print the values you get. It helps to know what the real data you are working with is, rather than hypotheticals.
p
Try this : SELECT resourceallocation.allocationResource FROM resourceallocation as resourceallocation WHERE SYS_EXTRACT_UTC(resourceallocation.startDate) <= SYS_EXTRACT_UTC(SYSTIMESTAMP) + (750 / 1440) AND SYS_EXTRACT_UTC(resourceallocation.endDate) >= SYS_EXTRACT_UTC(SYSTIMESTAMP) + (750 / 1440) GROUP BY resourceallocation.allocationResource
it converts everything to utc before comparing, should work
r
Thank you so much this is what I needed, will check and let you know.
p
👍
r
I was further checking on this today in depth. a few results are getting filtered out. When I further checked the timestamps. TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'MM/DD/YYYY HHMISS'), Is it possible for me to set the HHMISS part to lets say to 000000? Before I use it as filter for comparision? What is happening is I am not getting result for allocations with end date as 05/03/2023. (allocations record are created in IST timezone)
12 Views
Open in Slack
PreviousNext
Powered by