Deleting Historian Data

On daylight savings we had some duplicate data being entered into the database from 1:00 - 1:59 am. I would like to delete some of that data which is being stored in the historian or find some other way of dealing with it. Has anyone else come across a similar issue?

I’m confused. The Unix Timestamp that the Ignition Historian uses should avoid daylight savings time.

2 Likes

The straight way, using DELETE queries.
Can you confirm that, can you do a query that count duplicates time_stamps?

Isnt the TS stored as miliseconds since a set date in like 1979's?
This would in my mind not counter the daylight savings effect.

The easiest way is to do it directly in the database.
Just find the partition where the data is saved and delete the appropriate rows.

It is the milliseconds since midnight Jan 1, 1970.

Time is linear, just because at 2am some places decide they're going to now call it 1am, doesn't actually change how many milliseconds have passed. There aren't 2 day's a year that have 25 hours.

Are you sure the data is actually duplicated? Or is it possible that when the t_stamp is converted to a date the conversion is throwing things off?

2 Likes

I should clear something up, by duplicate data I initially meant data with the same time stamp - my mistake. The data itself is unique in that it was collected by the historian at the right time initially at 1 am and then again at 1 am after the time shifted back an hour. We decided to leave the data because it is still valid. And because we will lose an hour in the spring this ‘extra’ hour will be offset.

So you’re saying this data has the exact same t_stamp and tagid?

I find that very hard to believe as they are the index for the table, and there should have been a UNIQUE CONSTRAINT conflict error if Ignition (or anyone else for that matter) tried to insert duplicate records.

1 Like

The tag historian’s t_stamp columns use milliseconds UTC. They will be monotonically increasing through the two hours in question.

2 Likes

So the time is the same when I format from unix time to a readable format. But the unix time itself is different.