Eclipse Community Forums: Eclipse Process Manager (Stardust) » Use of Timestamps in Stardust AuditTrail database

Home » Eclipse Projects » Eclipse Process Manager (Stardust) » Use of Timestamps in Stardust AuditTrail database(Retrieving and using timestamps in Stardust AuditTrail database)

Show: Today's Messages :: Show Polls :: Message Navigator

Use of Timestamps in Stardust AuditTrail database [message #742083]

Thu, 20 October 2011 02:45

Srinivasan Iyer
Messages: 34
Registered: October 2011

Member

Stardust stores dates as long values in the Audit Trail. The statement below helps to check timestamps in a Stardust Audit Trail if Oracle DB is used.

e.g.

select to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( ai.starttime / (1000 * 60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS') "STARTTIME" from activitiy_instance ai;

[Updated on: Thu, 20 October 2011 04:25]

Report message to a moderator

Re: Use of Timestamps in Stardust AuditTrail database [message #742084 is a reply to message #742083]

Thu, 20 October 2011 02:45

Srinivasan Iyer
Messages: 34
Registered: October 2011

Member

Same SQL for MySQL databases looks like this ...

select cast(from_unixtime(ai.starttime / 1000) as datetime) from activity_instance ai

... considering timezone conversion ...

select convert_tz(cast(from_unixtime(ai.starttime / 1000) as datetime), '+00:00', '+05:00') from activity_instance ai

[Updated on: Thu, 20 October 2011 04:25]

Report message to a moderator

Re: Use of Timestamps in Stardust AuditTrail database [message #742086 is a reply to message #742084]

Thu, 20 October 2011 02:47

Srinivasan Iyer
Messages: 34
Registered: October 2011

Member

In case you need to add a where predicate on a date field that is stored as long value, you can use something similar to the SQL statement below (Oracle)...

SELECT count(*)
FROM process_instance pi
WHERE pi.state = 2
AND pi.oid = pi.rootprocessinstance
AND pi.terminationtime < (to_date('2010-01-01', 'YYYY-MM-DD') - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)

[Updated on: Thu, 20 October 2011 04:26]

Report message to a moderator

Previous Topic:	How to enable logging on transaction management for Spring managed Stardust runtime?
Next Topic:	Resetting daemon status at the database level

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 15:21:19 EDT 2013