We’ve configured webMethods 6.0.1 to log to a MS SQL Server 2000 database. Does anyone know the trick for converting the decimal datatype ‘audittimestamp’ in to a date which we can sort by?
Thanks,
Rich
Complements of one of SQL proficient team members (Steve Willard):
DECLARE @MSPERDAY INT
DECLARE @STARTDATE DATETIME
DECLARE @ENDTIME DATETIME
DECLARE @CHECKDAY BIGINT
DECLARE @CHECKMS BIGINT
SET @MSPERDAY = 246060*1000 – 24 hours/day * 60 Minutes/hour * 60 Seconds/Minute * 1000 Milliseconds/Second
SET @STARTDATE = ‘January 1, 1970’
SET @ENDTIME = ‘2003-09-22 21:15:00.000’
SET @CHECKDAY = DATEDIFF(DAY, @STARTDATE, @ENDTIME)
SET @CHECKMS = DATEDIFF(MS, CAST(CONVERT(CHAR(10),@ENDTIME, 102) AS DATETIME), @ENDTIME)
SELECT (@CHECKDAY * @MSPERDAY + @CHECKMS)
Thanks for the quick response! I need to figure out what all is going on there - and make an adjustment to the query above so we can use it to cast the decimal timestamp as a user-friendly date and so we can use it to select records within a certain timestamp range.
thanks again! If we figure out how to do the above I’ll post the query here.
The query posted in the reply above from sally_gerber was a great eye opener for me to get an understanding that the audittimestamp in the wmlogging tables (such as wmsession) is the milliseconds since 01/01/1970. Thanks for your help…
here’s what I’ve come up with:
SELECT DATEADD(ss,(audittimestamp/1000), ‘01/01/1970’) AS userfriendlytimestamp, * FROM wmsession
This query will work within 1 second of accuracy. We have to divide the millisecond audittimestamp by 1000 because SQL Server 2000 can’t calculate that many years worth of milliseconds. A little extra effort could produce a script that calculated the milliseconds since 01/01/2000 - then added 30 years - Or, you could build a pretty .NET, asp, java, or perl front end to do it.
–updated script to handle UTC (GMT) timezones.
/** SCRIPT: Get userfriendly timestamp for wmerror as column ts
FOR MS SQL SERVER
Author: Richard Bailey
NOTE: the - 05 in the query below represents the timezone.
change this value to correlate with the timezone your
database server resides in.
Also, the script will not work if you attempt to calculate
using milliseconds as MS SQL Server has limits to how large
the integer is for calculating milliseconds.
-you could do some additional math which you calculate the
date since the year 2000, then add 30 years… but this works
perfect for what we need it to do.
WHAT IT IS DOING?
-select all of the columns from wmerror table
-use the dateadd function to subtract - 05 hours (hh)
from the sub-dateadd value
-the sub-dateadd value is converting the millisecond
value from the audittimestamp value in to seconds (/1000)
then adding those seconds to 01/01/1970
**/
SELECT DATEADD(hh, - 05, DATEADD(ss, AUDITTIMESTAMP / 1000, ‘01/01/1970’)) AS ts, *
FROM WMERROR
____________script above this line
To make life easier on our team, I created SQL Server Views that run the script above for each of the tables (wmerror, wmsession, wmaudit, etc…) so that the team can simple run a query in analyzer or in their web pages that report the logs: something like “SELECT * FROM sqlview_wmerror WHERE ts BETWEEN ‘2003-10-14’ AND ‘2003-10-15’ ORDER BY ts”
Hope this helps someone.
Does anyone know how to do this in Oracle?
Matthew,
you can use the following statement in Oracle:
select to_char((to_date(‘1970-01-01 02:00:00’,‘yyyy-mm-dd hh24:mi:ss’ +AUDITTIMESTAMP/86400000),‘yyyy-mm-dd hh24:mi:ss’)
, SERVERID
, SERVICENAME
, ERRORMSG
from WMERROR
Maybe, you have to change the time 02:00:00.
You can check this by:
select to_char((sysdate - to_date(‘01-01-1970 02:00:00’,‘dd-mm-yyyy hh24:mi:ss’))*86400000 ) from dual;
Gr. Richard
to_date correction: need to add right ) before AUDITTIMESTAMP conversion
select to_char((to_date(‘1970-01-01 02:00:00’,‘yyyy-mm-dd hh24:mi:ss’) +AUDITTIMESTAMP/86400000),‘yyyy-mm-dd hh24:mi:ss’)
, SERVERID
, SERVICENAME
, ERRORMSG
from WMERROR
Hi,
In our case, in order to have a correct conversion audittimestamp → oracle date using SQL versus using java.util.Date class, we had to do this:
to_char((to_date(‘1970-01-01 03:00:00’,‘yyyy-mm-dd hh24:mi:ss’) +AUDITTIMESTAMP/86400000),‘yyyy-mm-dd hh24:mi:ss’)
Basically, we use the hour 03 instead of hour 02.
I do not know why…
May be it depends on the GMT thing.
I am in Eastern United States and couldn’t figure out the GMT offset for Oracle to_date() function. Instead of trying to figure it out, I subtracted 14400000 (4 hours in milliseconds) from the value stored in AUDITTIMESTAMP.
SELECT (to_date(‘01.01.1970’,‘dd.mm.yyyy hh24:mi:ss’) +((AUDITTIMESTAMP-14400000)/86400000)) AS ERRORDATE
FROM wmerror;
Here’s one I got while going through some SR in advantage…thought it might be of help to some…
Select to_char(to_date(‘01-JAN-1970’, ‘DD-MON-YYYY’) +
audittimestamp/(3600000*24), ‘DD-MON-YYYY HH:MI AM’) from wmerror;