Aggregate TIMESTAMP in Calculation View

[Former Member]

Dear all,

I have a huge table in my Calc View. All values are ordered by a TIMESTAMP, which differs by milliseconds (ten different values by second --> Format is: JJJJ-MM-DD hh:mm:ss.000). So I want to group the values now by second. I first tried the function ROUND() but it didnt work out. Does anyone else hav an idea??

Cheers, Christoph.

[former_member234877 · Accepted solution]

You can add a new column to your table using "generated always as" SQL extension and get rid of the second fractions.

Alter table table_name add (

TIME_2 SECONDDATE generated always as to_seconddate("TIME"))

When you use the new column in your views all the measures will be aggregated accordingly.

Regards,

Z

[Former Member]

Hi Christoph,

Will the EXTRACT function work for you?

http://help.sap.com/hana/html/sql_function_extract.html

[Former Member]

Hi Michael,

I solved the problem by the followeing sql statement:

select left("TIME",19), SUM ("KEYFIGURE0044"), SUM ("KEYFIGURE0045") FROM "TESTSCHEMA"."TESTTABLE" group by LEFT ("TIME", 19)

[lucas_oliveira]

Hi Christoph,

Did you tried to get the seconds between the column date and the current date? A little example for 4 days in seconds:

select now(), seconds_between(add_days(now(),-4),now()) from dummy

Hope it helps on your case.

Regards,

Lucas