How do I update a firebird timestamp field to remove milliseconds part?

Question

Is there a simple update statement I can execute to update a timestamp field in a firebird database to remove millisecond part.

so current value is: 9-DEC-2013 8:55:57.3560 i want to update it to be: 9-DEC-2013 8:55:57.0000

I need a generic update statement that calculates the new timestamp based upon the old timestamp.

Answer 1

There is no standard function in Firebird to do this. However with help of the built-in functions EXTRACT and DATEADD you will be able to construct this.

DATEADD(-1 * EXTRACT(MILLISECOND FROM theTimestamp) MILLISECOND TO theTimestamp)

The only problem with this solution is that it doesn't work correctly if your timestamps have a 100 microsecond part, as the documentation of DATEADD states:

<amount> ::= an integer expression (negative to subtract)

And the 100 microsecond part is a decimal fraction of the amount, and can't be changed this way. I have filed ticket CORE-4457 to have this changed, and starting with Firebird 3, DATEADD(... MILLISECOND TO ...) will now accept a fractional value.

However as a lot of client libraries only use or support millisecond precision this probably will not affect you (even the built-in CURRENT_TIME and CURRENT_TIMESTAMP are limited to millisecond precision).