I’m trying to show the difference the current time...
# generalj
jen
03/16/2021, 5:35 PMI’m trying to show the difference the current time and a datetime field in a Saved Search formula. Currently, my field is of type
Formula (Text)sysdate - {custrecord_my_datetime_field} || ''+000000000 01:34:12.0000000 day(s), 0 hour(s), 1 minute(s), 34 second(s)TRUNC(sysdate - {custrecord_my_datetime_field}) || ''g
Gregory Jones
03/16/2021, 6:24 PMj
jen
03/16/2021, 6:27 PMAs I said, TRUNC doesn’t work. The above only works with Dates, not Datetimes.
n
Netsuite Tragic
03/16/2021, 11:07 PM@jen you need your time difference as a number. Check SA18390 for some example calcs https://netsuite.custhelp.com/app/answers/detail/a_id/18390
j
jen
03/16/2021, 11:16 PMNo joy, that gives me an invalid expression
jen
03/16/2021, 11:17 PMmessage has been deleted
jen
03/16/2021, 11:17 PMmessage has been deleted
n
Netsuite Tragic
03/16/2021, 11:22 PMwhat's the field type of _last__alive_time?
j
jen
03/16/2021, 11:29 PMDate/Time
n
Netsuite Tragic
03/17/2021, 12:14 AMcan you explain that formula {today} - {_last__alive_time} what is your intention with the *1440
Netsuite Tragic
03/17/2021, 12:16 AMI am thinking {now} - {last_timestamp} so they will both be same format which can be subtracted to give you the interval
w
Watz
03/17/2021, 6:46 AM Copy code
extract(day from (systimestamp-{custrecord_datetime})) || ' day(s), ' || extract(hour from (systimestamp-{custrecord_datetime})) || ' hour(s), ' || extract(minute from (systimestamp-{custrecord_datetime})) || ' minute(s), ' || round(extract(second from (systimestamp-{custrecord_datetime}))) || ' second(s)'Watz
03/17/2021, 6:48 AMmessage has been deleted
Watz
03/17/2021, 7:18 AMIt looks like the field Date/Time is stored/displayed with my timezone gmt+1 and the systimestamp uses the server time gmt-8.
The comparison however is performed with both in the same timezone, which is good.
j
jen
03/17/2021, 3:04 PM@Netsuite Tragic the
*1440jen
03/17/2021, 3:09 PMBrilliant, thank you @Watz! Looks like my issue was using
sysdatesystimestamp2 Views