0
Answered

How do I convert the stored bigintvalue to a datetime?

Carol Wapshere 5 years ago updated by anonymous 5 years ago 3

I'm doing some data analysis and need to get a datetime value out of the IdB database where it is stored as a BigIntValue. How do I convert this to a readable format? I found a select query that would convert Unix time but it didn't work on these values (Arithmetic overflow error). Anyway the IdB values look different to those examples.

Answer

Answer
Answered

They are .NET ticks (UTC). TicksToTime.ps1

However, keep in mind that operations directly against the database are not supported and could also change/break between versions.

Answer
Answered

They are .NET ticks (UTC). TicksToTime.ps1

However, keep in mind that operations directly against the database are not supported and could also change/break between versions.

Thanks Adam! Don't worry, this is a one-off to help prioritise some data clean-up that needs to happen.

To summarise process:

- export values out to CSV

- the following powershell will convert to datetime:

foreach ($row in $CSV)

{

  $ticks = [long]$row.BigIntValue

  get-date $ticks

}