add 1 millisecond to timestamp

deb0687

Hi,

 

How can I add 1 millisecond to the current timestamp.

 

 

current_timestamp: 2015-06-17 11:19:35.000143

 

Requirement : 2015-06-17 11:19:35.000144

SruthiA

Hi,

 

  You can add 1 millisecond to the timestamp using the following query

 

select to_char(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS.MS') as old_timestamp , to_timestamp(to_char(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS.')||to_char(to_number(to_char(CURRENT_TIMESTAMP,'MS')) + 1)
,'DD-MM-YYYY HH24:MI:SS.MS') as new_timestamp;

 

dbadmin=> select to_char(CURRENT_TIMESTAMP,'dd-mm-yyyy HH24:MI:SS.MS') as old_timestamp , to_timestamp(to_char(CURRENT_TIMESTAMP,'dd-mm-yyyy HH24:MI:SS.')||to_char(to_number(to_char(CURRENT_TIMESTAMP,'MS')) + 1) ,'DD-MM-YYYY HH24:MI:SS.MS') as new_timestamp;

 

old_timestamp | new_timestamp
-------------------------+-------------------------
17-06-2015 19:40:04.634 | 2015-06-17 19:40:04.635

 

-Regards,

 Sruthi

Sharon_Cutter

Something like this would be simpler?

 

sharon.cutter=> select statement_timestamp(), statement_timestamp() + '1 millisecond'::interval;

      statement_timestamp      |           ?column?            

-------------------------------+-------------------------------

 2015-06-17 21:28:27.318451+00 | 2015-06-17 21:28:27.319451+00

(1 row)

 

 

 

deb0687

Thanks for the reply.Its working fine.

 

 

But finally I am using the below one.

 

 

to_timestamp('2015-06-16 14:05:27.398102','yyyy-mm-dd hh:mi:ss.us')+1/(24*60*60*1000000)