Ah, back in the good old days of Agile 8.5. Note that ITEM_HISTORY in the 8.5 schema has a field named TIMESTAMP. This should be what you want. But please note that it might not be quite the value you expect.

I can certify that all date and timestamp attribute values in Agile 9.X are stored as GMT values, and then altered for display by the time zone preference for each user so that the user sees the value that they expect. This allows use across international time zones and for folks to be able to better coordinate things. To extract the data from the database would require specifying a common timezone (PST, for example), and then all values would be shown from that timezone.

I am not so certain that the above applies to the Agile 8.5 schema. As I remember it (from over 15 years ago), date and timestamp values were stored per the timezone of the server, and there was no modification of date or timestamp values as users did not have a timezone assigned. So if I was in CST and logged into a server out on the west coast, I would see PST date/timestamp values and *not* CST values.

To get the create date from the database I would use :

select min(timestamp) from item_history where item = (select id from item where item_ number=’XYZ’) and action=8;

The “action=8” says to get the timestamp for when the part was created (and yes, there can be more than 1 record, so I use the MIN function).

If this does not work, what specifically are you trying to do? And which date values can you get?

Hello Kevin,

Thanks for your reply.

The query you gave fetches me just the date, not date with time.

Ex: for an item – create part – showing in Front UI is :- 4/10/2018 8:19:16 AM
but in item history table, in timestamp column it is fetching just the 4/10/2018.

I need to fetch the time values too along with date for “create part from” action in Agile 8.5 PLM.

Could you please help me out with this.

And that is what I forgot. The default date string does NOT include the time.

Execute the following statement in SQL*Plus or PL/SQL before running your select statement :

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;

This ensures that the hour:minute:second value is also shown from the TIMESTAMP value. Look up NLS_DATE_FORMAT and find all the other myriad ways to display the date and time in your SQL session. You can even make it a default by setting it just before you start SQL.

Yes John, I even tried that before, forgot to mention about it.

after setting up the NLS Data format, it is showing up time values but the query is fetching only 00:00:00 values.

It is not showing the correct values what Agile 8.5 front end is showing.

It is becoming really tough to find the time stamp values where it is stored in database (not in item history table – timestamp column).

Can you please help me out with this.

Thanks,

Okay, that is strange. The HH24:MM:SS should be showing in values because a date value does contain both date and time. And yes, the timestamp values ARE stored in the ITEM_HISTORY table, values from that table are what is displayed in the History tab for an item.

Use the query I provided above, execute it just before running your select statement, and see if things are okay. I use that in many places, and I always see the full date value. And actually, execute “select sysdate from dual” after setting the NLS_DATE_FORMAT value as I indicated above. There is no way that value will not have hours, minutes and seconds.

Yes Kevin, I tried exactly what you said.

select sysdate from dual is showing date with time values. But if i ran the item history query, it is showing date wit time values as 00:00:00

it is not fetching the time values for some reason. not sure what to do

Sorry Kevin, I tried changing NLS parameter by going to tools preferences database and nls parameters option.
Then I changed the data format.
Then I ran the item history query, it got resolved, it is showing the time values now.

Thanks a lot for your help. Much appreciated.