Difference between revisions of "Time Epochs"

From truxwiki.com
Jump to navigation Jump to search
 
(13 intermediate revisions by the same user not shown)
Line 12: Line 12:
 
! Sample
 
! Sample
 
! SQLite
 
! SQLite
 +
|-
 +
| .Net Ticks
 +
| <code>01 Jan 0001</code>
 +
| 100 Nanosecond
 +
| Integer
 +
| style="text-align:right;" | 18
 +
| style="text-align:left;" | 637702123019480000
 +
| <code>DATETIME(([time] / 10000000) - 62135596800, 'unixepoch')</code>
 
|-
 
|-
 
| Apple NSDate<br />Apple Cocoa Core
 
| Apple NSDate<br />Apple Cocoa Core
Line 17: Line 25:
 
| 1 Second
 
| 1 Second
 
| Integer
 
| Integer
−
| 9
+
| style="text-align:right;" | 9
 
| style="text-align:left;" | 656351501
 
| style="text-align:left;" | 656351501
 +
| <code>DATETIME([time] + 978307200, 'unixepoch')</code>
 +
|-
 +
| Apple NSDate<br />Apple Cocoa Core
 +
| <code>01 Jan 2001</code>
 +
| 1 Second
 +
| Float
 +
| style="text-align:right;" | 9+
 +
| style="text-align:left;" | 656351501.948449001
 
| <code>DATETIME([time] + 978307200, 'unixepoch')</code>
 
| <code>DATETIME([time] + 978307200, 'unixepoch')</code>
 
|-
 
|-
Line 25: Line 41:
 
| 1 Nanosecond
 
| 1 Nanosecond
 
| Integer
 
| Integer
−
| 18
+
| style="text-align:right;" | 18
 
| style="text-align:left;" | 656351501948449001
 
| style="text-align:left;" | 656351501948449001
 
| <code>DATETIME(([time] / 1000000000) + 978307200, 'unixepoch')</code>
 
| <code>DATETIME(([time] / 1000000000) + 978307200, 'unixepoch')</code>
 
|-
 
|-
−
| [[#Chrome|Chrome]]
+
| Chrome
 
| <code>01 Jan 1601</code>
 
| <code>01 Jan 1601</code>
 
| 1 Microsecond
 
| 1 Microsecond
 
| Integer
 
| Integer
−
| 17
+
| style="text-align:right;" | 17
 
| 13279089101948000
 
| 13279089101948000
 
| <code>DATETIME(([time] / 1000000) - 11644473600, 'unixepoch')</code>
 
| <code>DATETIME(([time] / 1000000) - 11644473600, 'unixepoch')</code>
 +
|-
 +
| libstdc++
 +
| <code>01 Jan 2174</code>
 +
| 1 Nanosecond
 +
| Integer
 +
| style="text-align:right;" | 19
 +
| -4803005298052000000
 +
| <code>DATETIME(([time] + 6437664000000000000) / 1000000000, 'unixepoch')</code>
 +
|-
 +
| PostgreSQL
 +
| <code>01 Jan 2000</code>
 +
| 1 Microsecond
 +
| Integer
 +
| style="text-align:right;" | 15
 +
| 687930701948000
 +
| <code>DATETIME(([time] + 946728000000000) / 1000000, 'unixepoch')</code>
 
|-
 
|-
 
| Unix
 
| Unix
Line 41: Line 73:
 
| 1 Second
 
| 1 Second
 
| Integer
 
| Integer
−
| 10
+
| style="text-align:right;" | 10
 
| style="text-align:left;" | 1634658701
 
| style="text-align:left;" | 1634658701
 
| <code>DATETIME([time], 'unixepoch')</code>
 
| <code>DATETIME([time], 'unixepoch')</code>
Line 49: Line 81:
 
| 1 Millisecond
 
| 1 Millisecond
 
| Integer
 
| Integer
−
| 13
+
| style="text-align:right;" | 13
 
| style="text-align:left;" | 1634658701948
 
| style="text-align:left;" | 1634658701948
 
| <code>DATETIME([time] / 1000, 'unixepoch')</code>
 
| <code>DATETIME([time] / 1000, 'unixepoch')</code>
Line 57: Line 89:
 
| 1 Microsecond
 
| 1 Microsecond
 
| Integer
 
| Integer
−
| 16
+
| style="text-align:right;" | 16
 
| style="text-align:left;" | 1634658701948449
 
| style="text-align:left;" | 1634658701948449
 
| <code>DATETIME([time] / 1000000, 'unixepoch')</code>
 
| <code>DATETIME([time] / 1000000, 'unixepoch')</code>
Line 65: Line 97:
 
| 1 Nanosecond
 
| 1 Nanosecond
 
| Integer
 
| Integer
−
| 19
+
| style="text-align:right;" | 19
 
| style="text-align:left;" | 1634658701948449001
 
| style="text-align:left;" | 1634658701948449001
 
| <code>DATETIME([time] / 1000000000, 'unixepoch')</code>
 
| <code>DATETIME([time] / 1000000000, 'unixepoch')</code>
 
|-
 
|-
−
| [[#UUID|UUID Type 1]]
+
| UUID Type 1
 
| <code>15 Oct 1582</code>
 
| <code>15 Oct 1582</code>
 
| 100 Nanosecond
 
| 100 Nanosecond
 
| [https://www.rfc-editor.org/rfc/rfc4122#section-4.1.4 Integer]
 
| [https://www.rfc-editor.org/rfc/rfc4122#section-4.1.4 Integer]
−
|
+
| style="text-align:right;" | 15
−
|
+
| b420ebc0-e26e-1389-8000-000000000000
−
|
+
| <code>DATETIME((([time] + 122192928000000000) / 10000000) - 11644473600, 'unixepoch')</code>
 
|-
 
|-
−
| [[#Windows_COM|Windows COM]]
+
| Windows COM<br />Excel OADate<br />OLE Automation Date
 
| <code>30 Dec 1899</code>
 
| <code>30 Dec 1899</code>
−
|
+
| 1 Day
−
|
+
| Float
−
|
+
| style="text-align:right;" | 5+
−
|
+
| 44488.1609021759
−
|
+
| <code>DATETIME(([time] * 86400) - 2209161600, 'unixepoch')</code>
 
|-
 
|-
−
| [[#Windows_FILETIME|Windows FILETIME]]
+
| Windows FILETIME
 
| <code>01 Jan 1601</code>
 
| <code>01 Jan 1601</code>
 
| 100 Nanosecond
 
| 100 Nanosecond
 
| Integer
 
| Integer
−
| 18
+
| style="text-align:right;" | 18
 
| 132790891019480000
 
| 132790891019480000
−
| <code>DATETIME( ([time] / 10000000) - 11644473600, 'unixepoch')</code>
+
| <code>DATETIME(([time] / 10000000) - 11644473600, 'unixepoch')</code>
−
|}
 
−
 
 
−
=Apple Cocoa Core=
 
−
[https://www.epochconverter.com/coredata Cocoa Core] (aka NSDate) is the number of seconds (32-bit value) since 01 Jan 2001.
 
−
The 64-bit version is the number of nanoseconds since 01 Jan 2001.
 
−
 
 
−
=Chrome=
 
−
Chrome stores the time as a 64-bit integer representing the number of microseconds since 01 Jan 1601.
 
−
 
 
−
=Firefox=
 
−
Firefox ([https://firefox-source-docs.mozilla.org/nspr/reference/prtime.html PRTime]) stores the time as a 64-bit integer representing the number of microseconds since 01 Jan 1970.
 
−
 
 
−
=Unix Time=
 
−
This is a 32-bit integer holding the number of clock ticks since 01 Jan 1970.
 
−
You will see this value in a variety of ticks.
 
−
 
 
−
==Hints==
 
−
The Unix epoch can be stored in several different clock ticks.
 
−
{| class="wikitable"
 
−
|-
 
−
! Digits
 
−
! Clock Tick Units
 
−
! Example
 
−
! SQLite
 
−
|-
 
−
| style="text-align:center;" | 10
 
−
| One Second
 
−
| style="text-align:left;" | 1634658701
 
−
| <code>DATETIME([time], 'unixepoch')</code>
 
−
|-
 
−
| style="text-align:center;" | 13
 
−
| One Millisecond
 
−
| style="text-align:left;" | 1634658701948
 
−
| <code>DATETIME([time] / 1000, 'unixepoch')</code>
 
−
|-
 
−
| style="text-align:center;" | 16
 
−
| One Microsecond
 
−
| style="text-align:left;" | 1634658701948449
 
−
| <code>DATETIME([time] / 1000000, 'unixepoch')</code>
 
−
|-
 
−
| style="text-align:center;" | 19
 
−
| One Nanosecond
 
−
| style="text-align:left;" | 1634658701948449001
 
−
| <code>DATETIME([time] / 1000000000, 'unixepoch')</code>
 
 
|}
 
|}
−
 
−
=UUID=
 
−
A [https://www.rfc-editor.org/rfc/rfc4122#section-4.1.4 60-bit integer] holding the number of 100-nanosecond clock ticks since 15 Oct 1582.
 
−
 
−
=Windows COM=
 
−
Windows [https://en.wikipedia.org/wiki/Component_Object_Model COM] stores date time in a double (floating point) number representing the number of days since 30 Dec 1899.
 
−
 
−
=Windows FILETIME=
 
−
A 64-bit integer containing the number of 100-nanosecond clock ticks since 01 Jan 1601.
 

Latest revision as of 09:15, 30 July 2026

Epochs

Here's some of the time formats that Truxton can handle. Wikipedia has a better list. The Truxton software uses FILETIME for all time calculations.

Name Epoch Clock Tick Data Type Digits Sample SQLite
.Net Ticks 01 Jan 0001 100 Nanosecond Integer 18 637702123019480000 DATETIME(([time] / 10000000) - 62135596800, 'unixepoch')
Apple NSDate
Apple Cocoa Core 		01 Jan 2001 1 Second Integer 9 656351501 DATETIME([time] + 978307200, 'unixepoch')
Apple NSDate
Apple Cocoa Core 		01 Jan 2001 1 Second Float 9+ 656351501.948449001 DATETIME([time] + 978307200, 'unixepoch')
Apple NSDate
Apple Cocoa Core 		01 Jan 2001 1 Nanosecond Integer 18 656351501948449001 DATETIME(([time] / 1000000000) + 978307200, 'unixepoch')
Chrome 01 Jan 1601 1 Microsecond Integer 17 13279089101948000 DATETIME(([time] / 1000000) - 11644473600, 'unixepoch')
libstdc++ 01 Jan 2174 1 Nanosecond Integer 19 -4803005298052000000 DATETIME(([time] + 6437664000000000000) / 1000000000, 'unixepoch')
PostgreSQL 01 Jan 2000 1 Microsecond Integer 15 687930701948000 DATETIME(([time] + 946728000000000) / 1000000, 'unixepoch')
Unix 01 Jan 1970 1 Second Integer 10 1634658701 DATETIME([time], 'unixepoch')
Unix 01 Jan 1970 1 Millisecond Integer 13 1634658701948 DATETIME([time] / 1000, 'unixepoch')
Unix 01 Jan 1970 1 Microsecond Integer 16 1634658701948449 DATETIME([time] / 1000000, 'unixepoch')
Unix 01 Jan 1970 1 Nanosecond Integer 19 1634658701948449001 DATETIME([time] / 1000000000, 'unixepoch')
UUID Type 1 15 Oct 1582 100 Nanosecond Integer 15 b420ebc0-e26e-1389-8000-000000000000 DATETIME((([time] + 122192928000000000) / 10000000) - 11644473600, 'unixepoch')
Windows COM
Excel OADate
OLE Automation Date 		30 Dec 1899 1 Day Float 5+ 44488.1609021759 DATETIME(([time] * 86400) - 2209161600, 'unixepoch')
Windows FILETIME 01 Jan 1601 100 Nanosecond Integer 18 132790891019480000 DATETIME(([time] / 10000000) - 11644473600, 'unixepoch')
Retrieved from "https://truxwiki.com/index.php?title=Time_Epochs&oldid=10983"