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)

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, Aug 08 '19 at 23:48
@Thomas `[datetime] '1970-01-01Z'`, which gives you a `Local` `[datetime]` instance; if you need a `Utc` instance, use `([datetime] '1970-01-01Z').ToUniversalTime()` — mklement0, May 04 '20 at 09:10
@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. — brianary, Aug 26 '21 at 00:17
@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).ToLocalTime()`. Alternatively, using `[datetimeoffset]` (which is preferable in general): `[datetimeoffset] '1970-01-01Z').AddSeconds($ctime).LocalDateTime` — mklement0, Aug 26 '21 at 01:36
@mklement0 Just to clarify, a UTC DateTime doesn't seem to work without some extra confusing steps, see the further discussion below, https://stackoverflow.com/a/10781745/54323 — brianary, Aug 28 '21 at 21:02

score 31 · Accepted Answer · edited Jan 14 '19 at 06:32

31

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)

edited Jan 14 '19 at 06:32

Peter Mortensen

30,738
21
105
131

answered May 28 '12 at 08:45

JohnB

13,315
4
38
65

11

`[datetime]$origin = '1970-01-01 00:00:00'` works just as well, and is maybe a little easier to understand – Torbjörn Bergstedt May 28 '12 at 08:54
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. – mrwh1t3 May 29 '12 at 00:42
1

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 Aug 26 '21 at 00:07
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).ToLocalTime()` 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. – mklement0 Aug 26 '21 at 01:44
@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 Aug 27 '21 at 03:59
@mklement0 Correction: DateTimeOffset does work, once converted to localtime. – brianary Aug 27 '21 at 18:20
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. – mklement0 Aug 27 '21 at 18:25
@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? – brianary Aug 28 '21 at 21:00
@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. – mklement0 Aug 28 '21 at 21:18

score 23 · Answer 2 · edited Jan 14 '19 at 06:23

23

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

edited Jan 14 '19 at 06:23

Peter Mortensen

30,738
21
105
131

answered Nov 06 '18 at 16:26

Fredrick

1,210
2
15
24

1

I feel stupid knowing that for the past years I've used [System.DateTime]::Parse('1-1-1970).AddSeconds($epoch) instead of DateTimeOffset – bluuf Nov 06 '18 at 17:07
No worries, it looks like the method is new as of .net 4.6! – Fredrick Nov 06 '18 at 18:13
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). – brianary Aug 26 '21 at 00:12
@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. – Fredrick Aug 26 '21 at 19:41
2

@Fredrick It looks like I can use `(([System.DateTimeOffset]::FromUnixTimeSeconds($unixTime)).DateTime.ToLocalTime()).ToString("s")` – brianary Aug 27 '21 at 18:19

score 19 · Answer 3 · edited Jul 13 '17 at 07:44

19

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.

edited Jul 13 '17 at 07:44

JackDMF

70
8

answered Jan 20 '14 at 13:00

uknzguy

409
3
4

score 8 · Answer 4 · answered Mar 22 '16 at 11:39

8

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

answered Mar 22 '16 at 11:39

VMwareWolf

91
1
1

score 4 · Answer 5 · edited Jan 14 '19 at 06:22

4

A simple one-liner:

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

edited Jan 14 '19 at 06:22

Peter Mortensen

30,738
21
105
131

answered Jan 15 '18 at 14:32

miljbee

300
3
8

score 4 · Answer 6 · answered Aug 04 '21 at 06:19

Not bringing anything new to the table, but a pipable version of Fredrics answer:

function epoch() { 
    Param( 
        [Parameter(ValueFromPipeline)]$epochTime,
        [Switch]$Ms
    ) 
    Process { 
        if ($Ms) {
            [System.DateTimeOffset]::FromUnixTimeMilliseconds($epochTime)
        } else {
            [System.DateTimeOffset]::FromUnixTimeSeconds($epochTime)
        }
    } 
}

"1628043561861" | epoch -Ms

DateTime      : 04.08.2021 02:19:21
UtcDateTime   : 04.08.2021 02:19:21
LocalDateTime : 04.08.2021 04:19:21
Date          : 04.08.2021 00:00:00
Day           : 4
DayOfWeek     : Wednesday
DayOfYear     : 216
Hour          : 2
Millisecond   : 861
Minute        : 19
Month         : 8
Offset        : 00:00:00
Second        : 21
Ticks         : 637636403618610000
UtcTicks      : 637636403618610000
TimeOfDay     : 02:19:21.8610000
Year          : 2021

score 1 · Answer 7 · answered May 28 '12 at 09:02

1

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

answered May 28 '12 at 09:02

mrwh1t3

349
1
4
13

score 1 · Answer 8 · answered Apr 21 '20 at 11:06

1

I wanted to be sure that I was calculating from UTC, so I added the SpecifyKind:

$epochStart = New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0
$epochStart = [DateTime]::SpecifyKind($epochStart,[DateTimeKind]::Utc)

$DateTimeUTC = $epochStart.AddSeconds($UnixTimestamp)

answered Apr 21 '20 at 11:06

NitrusCS

597
1
5
20

1

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` – mklement0 May 04 '20 at 09:18

score 1 · Answer 9 · answered Oct 08 '21 at 13:18

I found that neither of the suggested solutions solved my needs. So I adjusted and propose the following:

function ConvertFrom-UnixTime {
    [CmdletBinding(DefaultParameterSetName = "Seconds")]
    param (
        [Parameter(Position = 0, 
            ValueFromPipeline = $true, 
            Mandatory = $true,
            ParameterSetName = "Seconds")]
        [int]
        $Seconds,

        [Parameter(Position = 0, 
            ValueFromPipeline = $true, 
            Mandatory = $true, ParameterSetName = "Miliseconds")]
        [bigint]
        $Miliseconds
    )
    Begin {
        $date = (Get-Date "1970-01-01 00:00:00.000Z")
    }
    Process {
        switch ($PSCmdlet.ParameterSetName) {
            "Miliseconds" {
                $date = $date.AddMilliseconds($Miliseconds)
            }
            Default {
                $date = $date.AddSeconds($Seconds);
            }
        }
    }
    End {
        $date
    }
}
Set-Alias -Name epoch -Value ConvertFrom-UnixTime

Usage:

1633694244| epoch
1633694244565 | epoch

Both outputs:

8. oktober 2021 12:57:24

Time zone in function. It's great. The bulky function is easily reduced if desired. Thank you. — Garric, Dec 13 '21 at 13:05

score 0 · Answer 10 · answered Apr 13 '23 at 00:08

0

Everything I tried returned an error that the unix date was 'out of range'. Here is what finally worked for me:

PS> get-date 1681343082548 -UFormat "%A %m/%d/%Y %T %Z"

Tuesday 01/02/0001 22:42:14 -06

No quotes at all around the unix time.

answered Apr 13 '23 at 00:08

adamt8

308
1
7

score 0 · Answer 11 · answered May 30 '23 at 21:07

The DateTime type in PowerShell 7 has a static UnixEpoch value defined, so this can be done even more tersely now:

[DateTime]::UnixEpoch.AddSeconds($unixTime)

Note: This was introduced in .NET Core 2.1, so I believe it is applicable to PowerShell versions since 6.1.

Convert Unix time with PowerShell

11 Answers11

Linked

Related