Microseconds in timestamp : db2 query

Question

I want microseconds in timestamp so I am using below query but not getting the microseconds

select TIMESTAMP_FORMAT(CURRENT_TIMESTAMP,
       'YYYY-MM-DD HH:MM:SS.NNNNNN') FROM SYSIBM.SYSDUMMY1

Getting : 2013-06-01 02:45:12 Expected output:2013-06-01 02:45:12.145235

Ref:http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_bif_timestampformat.htm

Solution 1

It seems that you use wrong function, i.e. timestamp_format usage is:

TIMESTAMP_FORMAT function returns a timestamp that is based on interpreting the input string by using the specified format.

The functions receives string and returns timestamp - conversion is done following specification string. Example:

VALUES (TIMESTAMP_FORMAT('1999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
--------------------------
1999-12-31-23.59.59.000000

Conversion tst to string could be done like this:

VALUES cast(current_timestamp as varchar(40))
----------------------------------------
2013-06-05-23.19.15.125000

Getting only microseconds could be done by using microsecond function like this:

VALUES( microsecond(current_timestamp), current_timestamp)
----------- --------------------------
     468000 2013-06-05-23.21.10.468000

Note: microsecond returns number, not string.

Solution 2

use this piece of code to convert string datetime into timestamp format

 TIMESTAMP_FORMAT('2017-03-01 14:26:02.646171', 'YYYY-MM-DD HH24:MI:SS.NNNNNN')