Getting the number of seconds since midnight using SQL
Article ID: 213960
Updated On:
Products
Issue/Introduction
How can I get the number of seconds since midnight from CURRENT TIME or TIMESTAMP? Is there a function to manipulate/edit the timestamp?
Environment
Release : 15.1
Component : DATACOM SQL
Resolution
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 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:
//SQL0001 EXEC @SQL,PARM='INPUTWIDTH=80,PRTWIDTH=150'
//SYSIN DD *,SYMBOLS=JCLONLY
*$COLUMN
-- ********************************************************************
-- ** 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 Datacom if you have further questions.
Feedback
