Lennart wrote:
On Jun 23, 8:34 am, Irfan Mughal <iffi....@gmail.comwrote:
[...]
>thanks for ur reply . data type of "WRITETIME" is 16 character. data
is store like that 1082106400000000.
What is it, microseconds since 1970-01-01 00:00:00 UTC? In that case,
how about:
select ... from ... where timestamp('1970-01-01-00.00.00.0') +
(1082106400000000 / 1000000) seconds (current_timestamp - 24 hours)
/Lennart
[...]
It is a typical format for Tivoli, I guess, and definitely not "microseconds
since ..."
The example given by OP seems to be not valid, however (see below).
I found the following Redbook "Tivoli Management Services Warehouse and
Reporting":
http://www.redbooks.ibm.com/redbooks/pdfs/sg247290.pdf
which should be useful for OP. Be aware, though, it is a 9Mb download.
Here is some relevant snippets form a chapter on using Crystal Reports:
------------------------------
[Page 459:]
7.2.5 Report creation: Disk Usage
In this section, we create a sample report titled Disk Usage.
<description of some steps from the Crystal Reports wizard>
------------------------------
------------------------------
[Page 461, step 3 from the wizard:]
3. Select the grouping. In this case, we want the data to be grouped by
System_Name, by LAT_Mount_Point, and then by WRITETIME, as shown in Figure
7-36.
We also have to convert the WRITETIME field to a readable format. We do this
later when we finish this wizard.
------------------------------
------------------------------
[Page 463, step 5 from the wizard:]
5. At this point, we create an SQL Expression Field to convert WRITETIME to
a readable format.
To convert WRITETIME field, see "Converting TIMESTAMP field" on page 448.
------------------------------
------------------------------
[Page 449/450, the relevant part of above mentioned paragraph:]
3. Select SQL Expression Fields and enter an SQL that:
a. Converts the string field TIMESTAMP into a human-readable format string
You can use the following sample syntax to convert the TIMESTAMP field into
a readable format. This syntax translate the TIMESTAMP field to a
human-readable format:
From: 1061024164012000
To: 20061024:1640:12000
'20' || substr("Linux_CPU"."Timestamp",2,6) || ':' ||
substr("Linux_CPU"."Timestamp",8,4) || ':' ||
substr("Linux_CPU"."Timestamp",12,5)
b. Converts the string field TIMESTAMP into a datetime
You can use the following sample syntax to convert the TIMESTAMP field into
datetime format. This syntax translate the TIMESTAMP field to datetime
format:
From: 1061024164012000
To: 20061024164012 (System default: "10/24/2006 4:40:12PM")
timestamp('20' || substr("Linux_CPU"."Timestamp",2,6) ||
substr("Linux_CPU"."Timestamp",8,6) )
<...>
------------------------------
The format therefor seems to be CYYMMDDHHMMSSmmm
(C=Century-bit: 0=19, 1=20)
HTH
--
Jeroen