Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Community Member

SQL statement to convert Epoch time to Human Readable

I am looking for the SQL statement that can be used in a query (Query Analyzer) against the CDR database to convert the format from Epoch to MM/DD/YY 00:00:00.

I've googled "Unity Epoch time" (as mentioned on one forum posting) and even asked TAC, but no luck.

Any assistance would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Member

Re: SQL statement to convert Epoch time to Human Readable

Hi

The statement you are looking for is:

DATEADD(s, dateTimeOrigination, '19700101')

i.e. select DATEADD(s, dateTimeOrigination, '19700101'), duration, callingPartyNumber, originalCalledPartyNumber from callDetailRecord

As a bonus, you can also use

CONVERT(varchar(6),duration/3600)+':'+RIGHT('0'+CONVERT(varchar(2),(duration % 3600)/60),2)+':'+RIGHT('0'+CONVERT(varchar(2),duration%60),2)

to convert the duration from seconds to H:M:S

3 REPLIES
Community Member

Re: SQL statement to convert Epoch time to Human Readable

Hi

The statement you are looking for is:

DATEADD(s, dateTimeOrigination, '19700101')

i.e. select DATEADD(s, dateTimeOrigination, '19700101'), duration, callingPartyNumber, originalCalledPartyNumber from callDetailRecord

As a bonus, you can also use

CONVERT(varchar(6),duration/3600)+':'+RIGHT('0'+CONVERT(varchar(2),(duration % 3600)/60),2)+':'+RIGHT('0'+CONVERT(varchar(2),duration%60),2)

to convert the duration from seconds to H:M:S

Community Member

Re: SQL statement to convert Epoch time to Human Readable

Thank you, I'll try this today.

Community Member

Re: SQL statement to convert Epoch time to Human Readable

Thank you, this resolved my issue.

I used the DATEADD line and was able to print a readable report.

Debbie

8001
Views
0
Helpful
3
Replies
CreatePlease to create content