A Powershell function to convert unix time to string?

Question

I'm trying to read the date fields of Firefox places.sqlite datedase using ADO.NET and PowerShell.

Obviously these fields are in Unix time.

I'm looking for a conversion to .Net datetime or string

Edit: I want to bind the datarow to a WPF GridView.

I need either a way to do the conversion within the SQL query or some way while binding the datarows to the grid.

score 9 · Accepted Answer · answered Apr 25 '11 at 14:57

9

Something like this should put you on the right path:

[TimeZone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($unix))

answered Apr 25 '11 at 14:57

Keith Hill

I accept this answer, because it is the answer to the verbatime question. My problem was a bit different and meanwhile I found the solution – bernd_k Apr 25 '11 at 21:10

score 3 · Answer 2 · answered Apr 25 '11 at 14:57

3

[timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds(1303743289))

answered Apr 25 '11 at 14:57

Nicola Cossu

score 3 · Answer 3 · answered Apr 25 '11 at 15:07

function Convert-UnixTimeToDateTime([int]$UnixTime)
{
    (New-Object DateTime(1970, 1, 1, 0, 0, 0, 0, [DateTimeKind]::Utc)).AddSeconds($UnixTime)
}

This returns an object with the "kind" set to Utc. Then, if you want local time, call the ToLocalTime method on the resulting object.

score 2 · Answer 4 · answered Apr 25 '11 at 21:04

The working solution to my problem is

SELECT datetime (b.dateAdded / 1000000, 'unixepoch', 'localtime') dateAdded  from moz_bookmarks

BTW it is partly hidden in the the original documentation.

The other part is that you have to find out, that you must divide the integer by 1000000 to get the seconds.

4 Answers4