Time/ date Conversions and Duration Calculations in Omniscope

Modified on Wed, 7 Aug at 7:06 PM

Many datasets will contain time/date fields, and analysis often requires calculations or conversions to be performed on this type of data.

Omniscope allows easy date/time formatting in the Field Organiser block:

a) If the field is recognised as a date but the display value is not what you want, click on the 3-dot menu to change the output format. You can pick a desired format from the drop-down list or enter a custom format and click enter.

b) If the field is coming in as text and you want to format it as a date, change the data type from the drop-down menu ( do not worry if the values disappear briefly!), then use the 3-dot menu to type the field's input date pattern exactly as it appears in the incoming dataset, and select the desired output format.

As before - the output format can be different from the input pattern.

c) If the fields are formatted correctly but you wish to perform some date/ duration-related calculations - add a new field and click on the formula button to use one of the functions below. You can also combine the functions in a more complex formula.

DATE(year, month, day, hour, minute, second, millisecond, timezone)

DATEADD(date, number, unit)

DATEDIFF(date1, date2, unit, approximate)

DATETOTEXT(date_value, custom_format, time_zone)

DATEUNIT(date, unit)

DATEVALUE(text)

FORMATDURATION(number, unit) - available from build 21963

LASTDAYOFMONTH(date_value)

NETWORKDAYS(start_date, end_date)

NOW()

TEXTTODATE(text_value, custom_format, lenient, time_zone)

TODAY()

TZCORRECT(date, input_timezone, output_timezone)

TZDEFAULT()

TZOFFSET(time_zone, date)

UNIX_MILLISECONDS_TO_DATE(unixtime)

UNIX_SECONDS_TO_DATE(unixtime)

Click on the interactive formula guide for the full explanation of what the functions do and where each one can be applied.

Here's an example :

Let's say we have an event date/time and we wish to establish the duration of time that has passed since the recorded event (see the screenshot with data shown below)

Time difference between the two fields measured in seconds:

DATEDIFF([Field1],[Field2],"second")

Now convert [ Duration In Seconds] (numeric field) to HH:mm:ss format:

RIGHT("00" + INTFLOOR([Duration In Seconds] / 3600), 2) + ":" +

RIGHT("00" + INTFLOOR(MOD([Duration In Seconds], 3600) / 60), 2) + ":" +

RIGHT("00" + MOD([Duration In Seconds], 60), 2)