Obtaining The Difference Between Timestamps Or Microsecond Fields

The topic of how to obtain the difference or duration from timestamps having milliseconds has come up when working in Denodo a few times. So, after having clarified for different developers I thought I’d create this quick reference to clarify the subject little bit.

To get the difference with the duration you must subtract the start date field from the in the field (assuming you’re working with the after 1970), and then, divided by the microseconds the time unit in which you wish to display the result.

It’s basically boils down to:

• displaying the resultant milliseconds, no division is required
• displaying the results in seconds, divided by 1,000
• displaying the results in minutes, divided by 60,000
• displaying the results in hours, divided by 3,600,000

Example Denodo Code Shells

Here are some quick code shells with explanation above:

Duration in milliseconds

(gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>)) AS duration_millis

Duration seconds

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/1000) AS duration_seconds

Duration in minutes

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/60000) AS duration_minutes

Duration and hours

((gettimeinmillis(<<end_date_field>>)-gettimeinmillis(<<start_date_field>>))/36000000) AS duration_hours

Related References

Denodo > User Manuals > Virtual DataPort VQL Guide > Appendix > Syntax of Condition Functions > Date Processing Functions

• https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions

Denodo > Home > Q&A > TIMESTAMPDIFF

• https://community.denodo.com/answers/question/details?questionId=9060g000000L6rYAAS&title=TIMESTAMPDIFF