0
Answered

How do I convert the stored bigintvalue to a datetime?

Carol Wapshere 2 years ago • updated by anonymous 2 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.

Affected Versions:
Fixed by Version:

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

}