search cancel

Getting the number of seconds since midnight using SQL


Article ID: 213960


Updated On:


Datacom/DB Datacom


How can I get the number of seconds since midnight from CURRENT TIME or TIMESTAMP? Is there a function to manipulate/edit the timestamp?


Release : 15.1

Component : CA DATACOM SQL


By using the new function in 15.1 called TimeStampDiff (abbreviated TSD here), you can easily get this value. I have given a sample query below so you can see everything pretty clearly.

The first thing to note is that as a rule, the TSD function will not round the values, but will give a truncated value as with any other Decimal calculation - you are responsible for handling the rounding manually. Second, there are multiple timestamps you can work with: one in the MUF_Systems_Stats table, and the other as a system register. My examples are based on the system register.

To learn more about the TSD function, please see the Version 15.1 documentation for Scalar Functions. In addition to this, there is a Knowledge Base article I would recommend for you. Please read through Knowledge Base article 212993, titled "Using the CA Datacom/DB SQL function TIMESTAMPDIFF to compute dates with rounded results" when you have a chance, to find more uses for the TSD function.

Now, without further ado, here is the TSD-based query:

//SYSIN   DD  *,SYMBOLS=JCLONLY                                               
-- ********************************************************************       
-- ** 9. TSD: Number of seconds since midnight                       **       
-- ********************************************************************       
Select  '9. Num seconds since midnight'                           As Title    
-- ---------------------------------------------------------------------------
  ,Current Timestamp                                              As Now      
  ,Current Date                                                   As Dateonly 
-- ---------------------------------------------------------------------------
-- Display TimeStampDiff in truncated Seconds and Microseconds
-- TimeStampDiff will set the time to midnight (00:00:00.000000) if empty

  ,TimeStampDiff(SECOND,     Current Date, Current Timestamp)     As Second   
  ,TimeStampDiff(MICROSECOND,Current Date, Current Timestamp)     As Microsec 
-- ---------------------------------------------------------------------------
-- Round seconds using microseconds and adding half a second of Microseconds    
-- Then divide by a full second's microsecs                                   
  ,DEC((TimeStampDiff(MICROSECOND,Current Date, Current Timestamp)            
                     + 500000) / 1000000  ,5,0)                   As BySecond 
-- ---------------------------------------------------------------------------
 From SYSADM.MUF_Systems_Stats MSS;                                           

Additional Information

As always, please contact Broadcom support for CA Datacom if you have further questions.