Adding millisecond to date in SAP HANA

To add milliseconds to a date in SAP HANA, you can use the ADD_SECONDS function with fractional values. Since there are 1000 milliseconds in a second, you can pass decimal values to represent milliseconds.

Using ADD_SECONDS Function

Try using ADD_SECONDS function as below ?

SELECT ADD_SECONDS(TO_TIMESTAMP('2017-07-15 02:17:15'), 0.1) FROM TEST;

This will add 100 milliseconds (0.1 seconds) to the timestamp. The function accepts fractional values where 0.001 equals 1 millisecond.

Syntax

ADD_SECONDS(t, n)

Description: Computes the time t plus n seconds, where n can be a fractional value to represent milliseconds.

Example

Here's a practical example that adds 30 minutes (1800 seconds) to a timestamp ?

SELECT ADD_SECONDS(TO_TIMESTAMP('2012-01-01 23:30:45'), 60*30) "add seconds" FROM DUMMY;

The output of the above code is ?

add seconds
2012-01-02 00:00:45.0

Adding Milliseconds Example

To add specific milliseconds, use fractional seconds ?

SELECT ADD_SECONDS(TO_TIMESTAMP('2017-07-15 02:17:15'), 0.500) "add milliseconds" FROM DUMMY;

This adds 500 milliseconds to the timestamp.

Additional Resources

To know more about ADD_SECONDS function, you can also refer this link: datetime function in SAP HANA

To know more about SQL functions in SAP HANA system, you can also refer our HANA tutorial: SAP HANA SQL Functions

Conclusion

The ADD_SECONDS function provides a simple and effective way to add milliseconds to dates in SAP HANA by using fractional second values.