Friday, September 28, 2012

PowerDesigner: How to determine "create" and "modify" timestamps in XDB files

Have you ever wondered who made a change to a PowerDesigner XDB file and when the change was made?  Here's how to figure that out.

Open an XDB file in a text editor.  Search for CreationDate or ModificationDate.  Following these XML tags you will find Creator or Modifier tags; these will give the logon id (OS user) of the person who created or modified the attribute.

To get the date of creation or modification, create an Excel spreadsheet similar to the one shown below.  In the "Seconds" column, insert the number found in the XDB file such as:

<a:ModificationDate>1348840976</a:ModificationDate> (Column A4.)

Divide this number by 86,400, the number of seconds in a day ("Days", B column).
  • B column formula: =A4/86400
PowerDesigner counts the number of seconds from Midnight on January 1, 1970 UTC.  In Excel, add the number of fractional days to this date to get the calculated date in the UTC time zone.

To calculate the local time, add or subtract your time zone offset.  For me in the U.S. Central time zone, during daylight savings time, I need to subtract 5 hours, or 5/24 of a day.  The formula I use is below.
  • D column formula: =(C4+B4)-(5/24)
Of course, the formulas above would have their rows changed in each row, but you should get the idea.  You would also have to use a "custom" cell format to get the dates to display as shown below.  I use "m/d/yyyy h:mm:ss" as a custom format for dates in columns C and D.

1 Seconds Days Starting Date UTC CDT
2 1325765647 15344.5098 1/1/1970 0:00:00 1/5/2012 7:14:07
3 1330036842 15393.9449 1/1/1970 0:00:00 2/23/2012 17:40:42
4 1348840976 15611.5854 1/1/1970 0:00:00 9/28/2012 9:02:56
5 1348842034 15611.5976 1/1/1970 0:00:00 9/28/2012 9:20:34

Hope you find this useful.

No comments:

Post a Comment