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

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

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

Thu, 20 October 2011 02:45

Eclipse User

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] by Moderator

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

Thu, 20 October 2011 02:45

Eclipse User

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] by Moderator

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

Thu, 20 October 2011 02:47

Eclipse User

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] by 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 [=-

Current Time: Mon Jul 07 16:48:24 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter