- UNIFYBroker Forum
-
Questions
How do I convert the stored bigintvalue to a datetime?
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
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
}
Customer support service by UserEcho
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.