Add Seconds to Timestamp

ONE DATA Core
1

Hi Community,

I have a Data Table that includes a column with a timestamp and another column with a number of seconds. Now I want to add that number of seconds to the timestamp.
One solution I have found is to convert the timestamp to integer (which converts it into seconds), add the other column, and then convert the sum of both back to datetime. This works, but I’d imagine there is an easier way.

Other things I have considered/tried:

  • Mathematical Timestamp Operation Processor: Only takes a fixed operand or Workflow variable but no value from a column
  • SQL: I found 2 functions online called “DATEADD” and “DATE_ADD”. The first one would work but is unavailable in OD/Spark SQL. The second one is available but can only add an integer number of days so it’s useless for adding seconds

Maybe someone has an idea for a more clean way to do this :smiley:

1 Like
2

You can use INTERVAL as following
SELECT (timestamp + INTERVAL 55 seconds) as newtime FROM inputTable i

3

This does not seem to work if I replace the “55” with the name of the column that I want to use for the addition

4

Are both columns you are using as operands for the sum operation formatted as double or integers, or is either column a string? If they are a string, I doubt the sum would work.

Also, what exactly does this operation return when you state it does not work? NULL? Empty value? A wrong value?

5

The timestamp column is formatted as datetime, the column with the seconds to add is integer.

SELECT (timestamp + INTERVAL seconds_to_add seconds) AS newtime FROM inputTable i
throws
Exception in processing: IllegalStateException: ParseException: mismatched input 'seconds' expecting ','(line 1, pos 60) == SQL == SELECT (timestamp + INTERVAL seconds_to_add seconds) AS newtime FROM temptable76c5efc8166749123cf69f4dfe4762b2 i

Not including the “seconds” throws
Exception in processing: IllegalStateException: ParseException: mismatched input ')' expecting ','(line 1, pos 59) == SQL == SELECT (timestamp + INTERVAL seconds_to_add seconds) AS newtime FROM temptablec427ecba65054b4388a0d6cd5bd04c32 i

6

Uhm, here is a useful page with some example about it that I used in the past. I’ve been trying several different ways and methods, but didn’t find any way to add a column containing seconds to a column containing a timestamp or string. I always get the same weird errors you are getting.

Searching online, some users say that in Spark, you can only add a static numeric quantity to a column, and dynamic columns’ adding for timestamps isn’t supported. Very weird :face_with_diagonal_mouth: Well, if Spark SQL doesn’t work, the last resort would then be using Python.