How To Convert a Timestamp Into a Legible Format Using Cisco WAN Manager


On a Unix system, a timestamp is the number of seconds that have elapsed since 1 January 1970, called the "Epoch." When a statistic is stored in the Cisco WAN Manager (CWM) database, it receives the timestamp at which the data was collected from the switch. This timestamp sometimes has to be translated into a more readable format. This document explains several methods to convert the timestamp into a more readable format. The examples use the packet_ln_data table.



There are no specific requirements for this document.

Components Used

The information in this document is based on these software and hardware versions:

  • CWM 9.2.09 to CWM 12.0

  • Solaris 2.6 to Solaris 8

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.


For more information on document conventions, refer to the Cisco Technical Tips Conventions.

Using a Spreadsheet

If you use a Microsoft Excel spreadsheet, you can translate the timestamp with this method:

  1. Put the timestamp in cell a1.

  2. In cell a2, enter =a1/86400+25569.

  3. To apply a new format to this cell, right-click and choose Format cells > Numbers > Custom. In the Type field, enter dd:mmm:yyyy hh:mm.

    The output looks like this:

    04 Apr 2001, 13:57

Using SQL Queries

Type this at the shell prompt (as user svplus):

echo 'select date(timestamp/86400 +25568) || " " || mod ( trunc (timestamp/3600 ) , 24 ) || ":" || mod ( trunc ( timestamp/60 ) , 60 ) || ":" || mod ( timestamp, 60 ) from packet_ln_data' | dbaccess stratacom

This method produces an output that looks like this:

(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:5:0
(expression)  11/14/2001 16:10:0
(expression)  11/14/2001 16:10:0
(expression)  11/14/2001 16:10:0

Using an SQL Procedure

Another procedure is to add this procedure to the stratacom database:

  1. Run dbaccess.

  2. In the main menu, go to Query-language > New and select the Stratacom database.

  3. Paste the text, then select Escape.

  4. Select Run.

If the procedure is successful, you see the system message: Procedure created .

create procedure ts2date ( l int ) returning char(20);
   define sec, min, hour int;
   define ts char(10);
   define brol char(30);
   define chour, cmin, csec char(2);
   let sec = MOD (l, 60);
   let min = MOD (trunc (l/60), 60);
   let hour = MOD (trunc (l/3600), 24);
   if sec >= 10 then
      let csec  = sec;
      let csec = '0' || sec;
   end if   if min >= 10 then
      let cmin  = min;
      let cmin = '0' || min;
   end if   if hour >= 10 then
      let chour  = hour;
      let chour = '0' || hour;
   end if
   let Ts = date(trunc((l/86400) + 25568 ));
   let brol = Ts || ' ' || chour || ':' || cmin || ':' || csec;
   return brol;
end procedure;

Use this ts2date procedure in an SQL query to get the correct output. As the svplus user, type at the shell prompt:

echo 'select ts2date(timestamp) from packet_ln_data' | dbaccess stratacom

The output for this query looks like this:

11/14/2001 16:05:00     
11/14/2001 16:05:00     
11/14/2001 16:05:00     
11/14/2001 16:05:00     
11/14/2001 16:05:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00     
11/14/2001 16:10:00

Note: You must reinsert the procedure in the database each time it is dropped, after re-installing CWM, or after a cold start.

Using a C System Call

If you need to convert the timestamp with a C program or function, there is a system call that converts the Epoch notation into a more readable one. The system call is known as "ctime," and the parameter must be of type "const time_t *clock." For example:

#include <stdio.h>
#include <time.h>
void main ()
    time_t tstamp;
    scanf("%ld", &tstamp);
    printf ("%s\n", ctime (&tstamp));


There is currently no verification procedure available for this configuration.


There is currently no specific information available to troubleshoot this configuration.

Related Information