Convert Unix time with PowerShell

Question

I am parsing an SQLite database using the PowerShell SQLite module, and a couple of the return values are created and modified, both of which are in Unix time.

What I would like to do is somehow convert that into "human time". I have removed some of the other SQL queries for ease of reading.

Import-Module SQLite
mount-sqlite -name GoogleDrive -dataSource E:\Programming\new.db
$cloud_entry = Get-ChildItem GoogleDrive:\cloud_entry
foreach ($entry in $cloud_entry)
{
    $entry.created
}

The output looks like a large column of Unix timestamps:

1337329458

Update: I ultimately went with the following:

$ctime = $entry.created
[datetime]$origin = '1970-01-01 00:00:00'
$origin.AddSeconds($ctime)

Solution 1

See Convert a Unix timestamp to a .NET DateTime.

You can easily reproduce this in PowerShell.

$origin = New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0
$whatIWant = $origin.AddSeconds($unixTime)

Solution 2

Function Convert-FromUnixDate ($UnixDate) {
   [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($UnixDate))
}
$niceTime = Convert-FromUnixDate $ctime
PS C:\> $niceTime
Friday, 18 May 2012 8:24:18 p.m.

Solution 3

Use:

(([System.DateTimeOffset]::FromUnixTimeSeconds($unixTime)).DateTime).ToString("s")

FromUnixTimeMilliseconds is also available.

ToString("s"): Sortable: "The pattern reflects a defined standard (ISO 8601)"

Ref.: Standard Date and Time Format Strings, The Sortable ("s") Format Specifier

Solution 4

$date = get-date "1/1/1970"
$date.AddSeconds($unixTime).ToLocalTime()

Solution 5

A simple one-liner:

(Get-Date "1970-01-01 00:00:00.000Z") + ([TimeSpan]::FromSeconds($unixTime))

View more solutions

Comments

[mklement0]

What you went with is ambiguous, because something like [datetime] '1970-01-01 00:00:00' creates a [datetime] instance whose .Kind property is Unspecified. By contrast, the start of Unix epoch time is unambiguously UTC.

[mklement0]

@Thomas [datetime] '1970-01-01Z', which gives you a Local [datetime] instance; if you need a Utc instance, use ([datetime] '1970-01-01Z').ToUniversalTime()

[brianary]

@mklement0 Using a UTC start time for a value during daylight saving time is giving me a result that is off by an hour (the DST offset). I think using that method is doing the time zone calculation based on the start datetime, instead of the ending datetime.

[mklement0]

@brianary You're correct: the correct solution is to get a Utc [datetime] instance, add the Unix epoch time (in seconds) to it, and then convert to local time: ([datetime] '1970-01-01Z').ToUniversalTime().AddSeconds($ctime).ToLocalT‌​ime(). Alternatively, using [datetimeoffset] (which is preferable in general): [datetimeoffset] '1970-01-01Z').AddSeconds($ctime).LocalDateTime

[brianary]

@mklement0 Just to clarify, a UTC DateTime doesn't seem to work without some extra confusing steps, see the further discussion below, stackoverflow.com/a/10781745/54323

[Torbjörn Bergstedt]

[datetime]$origin = '1970-01-01 00:00:00' works just as well, and is maybe a little easier to understand

[mrwh1t3]

I'm getting this error: Any ideas? Cannot convert argument "0", with value: "", for "AddSeconds". Some of the values are "null", in that some of the items in the database are not assiged a created/modified date. I'm assuming this is where that error is coming from.

[bluuf]

I feel stupid knowing that for the past years I've used [System.DateTime]::Parse('1-1-1970).AddSeconds($epoch) instead of DateTimeOffset

[user2190601]

No worries, it looks like the method is new as of .net 4.6!

[mklement0]

Good idea to request a UTC time, but you can do that simply by passing another argument to the New-Object call: New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0, Utc

[brianary]

The time zone is going to be wrong unless you add .ToLocalTime() to the end of that second line. You can't use [datetime]'1970-01-01Z' because that'll do the time zone offset of the start time, which will be off during daylight savings.

[brianary]

This doesn't seem to work. For a value of 1629937097, I'm getting 2021-08-26T00:18:17 instead of 2021-08-26T17:18:17 (see epochconverter.com or a similar reference converter).

[mklement0]

Good point, @brianary: [datetime] '1970-01-01Z' by itself isn't enough, because it returns a Local [datetime] instance, which locks in the DST offset at the start time. Instead, use ([datetime] '1970-01-01Z').ToUniversalTime().AddSeconds($unixTime) to get the time as a UTC time stamp, and ([datetime] '1970-01-01Z').ToUniversalTime().AddSeconds($unixTime).ToLoc‌​alTime() to convert to a Local one. Or, using [datetimeoffset], which is preferable in general: ([datetimeoffset] '1970-01-01Z').AddSeconds($unixTime), then access .UtcDateTime or .LocalDateTime, as needed.

[user2190601]

@brianary The powershell output will be UTC in 24h format. I'm seeing 2021-08-26T00:18:17 from powershell and from epochconverter (top field) GMT: Thursday, August 26, 2021 12:18:17 AM which is equivalent since the converter output in is 12h time.

[brianary]

@mklement0 This works fine (Get-Date 1970-01-01).AddSeconds($unixTime).ToLocalTime(), without the confusion of code creating a UTC DateTime and then converting that to UTC again. The DateTimeOffset approach also gave me the wrong answer.

[brianary]

@Fredrick It looks like I can use (([System.DateTimeOffset]::FromUnixTimeSeconds($unixTime)).D‌​ateTime.ToLocalTime(‌​)).ToString("s")

[brianary]

@mklement0 Correction: DateTimeOffset does work, once converted to localtime.

[mklement0]

You're right @brianary (the alternative is ([datetime] '1970-01-01').AddSeconds($unixTime).ToLocalTime()), but there is a pitfall: Get-Date 1970-01-01 (and [datetime] '1970-01-01') create an Unspecified [datetime] instance. While this happens to work with .ToLocalTime(), if you need a local instance (because an Unspecified instance is then treated as UTC), it misbehaves if you call .ToUniversalTime() (because Unspecified is then like Local). Working with Unspecified instances can introduce subtle bugs.

[brianary]

@mklement0 Unspecified is exactly what you want from a start time, since you don't want the DateTime to calculate the time zone offset until you've calculated the final DateTime. ([datetime] '1970-01-01').AddSeconds($unixTime).ToLocalTime().Kind returns Local, so the result of the expression isn't vulnerable to any Unspecified bugs. Do you have an example of a value that would cause a problem?

[mklement0]

@brianary, please see my previous comment for an example (to recap: if you want a Utc instance from an Unspecified one, .ToUniversalTime() assumes that Unspecified means Local) - my recommendation is to stay away from Unspecified, for the reasons stated, and, taking a step back, to work with [datetimeoffset] whenever possible. Yes, if you know you want a local timestamp and you're aware of the general pitfalls, you can use your shortcut - but I wouldn't recommend it. I think we've covered all angles now.

[Garric]

Time zone in function. It's great. The bulky function is easily reduced if desired. Thank you.