Trying to get the hours time difference between two Date/Time fields in netsuite and can't quite figure out the right pattern to do that

You can calculate the Unix Timestamp

SELECT (to_date(sys_extract_utc(systimestamp)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(systimestamp), 'SSSSS') FROM DUAL

@Shai Coleman I have two custom date/time fields: CUSTOMRECORD_DC_TC_PUNCH.custrecord_dc_tc_punch_datetime_in CUSTOMRECORD_DC_TC_PUNCH.custrecord_dc_tc_punch_datetime_out Not sure how to make what you sent work using these

So the example I sent was with

systimestamp

, you need to change it to your field of choice, and same with the other field, and then subtract the two, and you'll get the difference in seconds

(to_date(sys_extract_utc(CUSTOMRECORD_DC_TC_PUNCH.custrecord_dc_tc_punch_datetime_out)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(CUSTOMRECORD_DC_TC_PUNCH.custrecord_dc_tc_punch_datetime_in), 'SSSSS') So I have this but not sure what to do with the 1970 date part. Which value goes there?

You keep it as 1970-01-01

I don't want seconds since 1970 but seconds between those two fields

Here's the full example

SELECT (to_date(sys_extract_utc(lastmodifieddate)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(lastmodifieddate), 'SSSSS') -
       (to_date(sys_extract_utc(createddate)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(createddate), 'SSSSS')
FROM transaction

hmm that gave me 82800 which is 24hrs and it should be 1hr

Was missing parentheses

SELECT  ((to_date(sys_extract_utc(lastmodifieddate)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(lastmodifieddate), 'SSSSS')) -
       ((to_date(sys_extract_utc(createddate)) - to_date('1970-01-01', 'YYYY-MM-DD')) * 86400 + to_char(sys_extract_utc(createddate), 'SSSSS'))
FROM transaction

Sweet!!! Thanks that worked perfectly. Been trying to figure this out for a while

regex solution, just because:

TO_NUMBER(REGEXP_SUBSTR((lastmodifieddate - createddate)*24,'^[+-]\d+'))

• timestamp-timestamp gives a 'DAY to SECOND' interval type • multiplying it by 24 gives it in hours • then wrapping it in a

TO_NUMBER(REGEXP_SUBSTR(...))

gives us the (whole) hours. see screenshot (timestamps are displaying as 'day hour-min-sec')

@David B awesome, thanks David. Another great solution.

yeah, this method only gives you the whole hours, effectively truncating the interval

Convert to epochs.

isn't that what Shai did? Is there a more tidy/compact way to do so?

You can make it a tiny bit shorter by using the

DATE

keyword.

SELECT ((to_date(sys_extract_utc(lastmodifieddate)) - DATE '1970-01-01') * 86400 + to_char(sys_extract_utc(lastmodifieddate), 'SSSSS')) -
       ((to_date(sys_extract_utc(createddate)) - DATE '1970-01-01') * 86400 + to_char(sys_extract_utc(createddate), 'SSSSS'))
FROM   transaction