0

I want to calculate sum of activity time(HH:mm:ss) for various transactions in PDI. For example, consider 3 activity times: 1) Activity 1 - 01:22:03, 2) Activity 2 - 01:10:11 and 3) Activity 3 - 00:22:20. The sum of all this time should be 02:54:34 but the result displays negative value. How would I improve it?

ParagCreative
  • 1
  • 4

1 Answers1

1

I ran into your issue (and the solution) almost by accident. It's worth explaining in a bit of detail.

Date fields are not meant for durations. They define instants. If you define a date field without its date part you're actually defining it as an instant on 1 Jan 1970, which is Unix time's start.

So, if you take your first timestamp, when you set 01:22:23 as a date field you're actually setting it to be "1 Jan 1970 01:22:23". You'd expect this field to return 4923 it's value in seconds (e.g., using getTime() on Javascript). This would work out nicely for your calculations; you could then add them up, and re-format to display.

However, if you don't specify the Timezone when setting the date value, then the date field will use your LOCAL timezone settings to define that time.

So, if you're in NY timezone, defining 01:22:23 as a Date field with format HH:mm:ss returns "31 Dec 1969 6:22:23 UTC", which returns 22923 in seconds.

If you're in Paris or another city which was ahead of UTC on 1 Jan 1970, some or all of your durations may return negative values.

The reason I say I ran into it by accident is because I'm based in London, which should be on UTC in the winter. However, oddly, that was not the case in 1970 (see UNIX timestamp(0): Europe/London returns UTC+1)

So, when calculating the timestamps in London timezone, I got:

Local time, seconds in Unix time

1:22:03, 1323

1:10:11, 611

0:22:20, -2260

These numbers add up to -326.

My suggestion:

  1. Don't define durations as dates, or timestamps; that's not what they are. Durations are time intervals. A 1h duration is worth the same regardless of the year, day or timezone you measure it in.

  2. Instead, just parse the values in a javascript step and do the math without resorting to date parsing.

Hacks to get around the problem (which I don't recommend):

  • explicitly set the timezone as +0000 when converting the fields to dates;

  • change your computer's timezone to UTC.

nsousa
  • 4,448
  • 1
  • 10
  • 15
  • _Thank you for your answer @nsousa. We attempted the same using javascript(getTime()) method as advised by you but it gives negative values. We also attempted to keep our raw data for activity time in number format in order to get expected results for sum(activity time); still we get negative values._ – ParagCreative Dec 22 '17 at 09:59
  • That's exactly what I warned you against: You can't simply use getTime() on a time-only value, without specifying the timezone. That function returns the timestamp interpreting that time as occurring on 1 Jan 1970 on your local timezone. If your local timezone on 1 Jan 1970 was AHEAD of UTC, some times will return negative timestamps. You would need to add or subtract the timezone offset. – nsousa Dec 22 '17 at 10:10
  • Just parse it as a string: assuming it's on HH:mm:ss format, split by ':'; multiply the hours by 3600, multiply the minutes by 60, and add them all up. Then re-format to display again. but you CANNOT parse a time-only string as a date field and expect correct results unless you specify UTC (and even then, you shouldn't). – nsousa Dec 22 '17 at 10:11
  • Thank you buddy, the second option worked out for me. Great Solution ! – ParagCreative Dec 27 '17 at 14:06