Timestamp converter that names the format first

Almost certainly Windows FILETIME or .NET ticks, and the leading digits separate them: FILETIME values for dates in this decade start with 13, while .NET ticks start with 63. Both count 100-nanosecond intervals, but from different origins — 1 January 1601 for FILETIME and 1 January 0001 for .NET — so the same date produces numbers roughly five times apart. An 18-digit value that is neither is probably nanoseconds since 1970, which for a current date starts with 17.

RFC 3339 is a strict subset of ISO 8601 with the optional parts removed. ISO 8601 allows week dates like 2024-W11-5, ordinal dates, the separator-free basic form 20240315T034642Z, a comma as the decimal marker and a local time with no offset at all; RFC 3339 demands a full calendar date, a T or a space, and an offset that is either Z or ±HH:MM. In practice that means every RFC 3339 timestamp is valid ISO 8601 and plenty of ISO 8601 is not valid RFC 3339, which is exactly the gap a parser falls into when an API sends a week date.

Yes — the first four bytes of the twelve are a big-endian Unix second count, so the leading eight hex characters convert straight to a date. 65f3c4a2 is 1710474402, which is 15 March 2024 at 03:46:42 UTC. The remaining eight bytes are a random per-process value and a counter and carry no time at all, which is why the ObjectId row in the table above pads them with zeros. Resolution stops at one second, so two documents written in the same second cannot be ordered by their timestamps alone.

Because Excel believes 1900 was a leap year. Lotus 1-2-3 shipped that bug in 1983, Excel copied it deliberately so that spreadsheets would interoperate, and serial 60 is therefore 29 February 1900 — a day that never existed. Every serial from 1 to 59 is consequently one greater than the true day count and nothing before 1 March 1900 converts cleanly, which is why the table above refuses to print a serial for those dates rather than printing a wrong one. Apple made things worse for a while by shipping a 1904 date system in Mac Excel, so a workbook moved between platforms could shift by 1,462 days.

The one in the selector, and that choice changes the answer. A MySQL DATETIME or a SQL Server datetime2 stores a wall-clock reading with no offset attached, so the string 2024-03-15 03:46:42 is not a moment until somebody names a zone — read in Berlin it is an hour earlier in absolute terms than read in London. The converter shows both the zone reading and the UTC reading as separate candidates for that reason, and if the value falls in an hour that a daylight-saving change repeated, it says which of the two passes it took.

Apple absolute time counts from 1 January 2001, the epoch Cocoa picked for CFAbsoluteTime and NSDate, so any date before that year comes out below zero. You meet it in plist files, Core Data stores, Safari history databases and iOS backups, usually as a float with a fractional part. Its cousin still turns up too: HFS+ and the classic Mac OS counted seconds from 1904, which is why a file copied off an old Mac volume can carry a timestamp about 2.08 billion higher than the Unix value for the same moment.

It tries every reading and keeps the ones that land on a believable date. The same digits are interpreted as Unix seconds, milliseconds, microseconds and nanoseconds, as FILETIME, as .NET ticks, as an Excel serial and against the 2001 and 1904 Apple epochs; anything falling outside 1900 to 2200 is discarded outright, and anything between 1995 and 2045 is promoted to a first answer. That is why a value can be reported with confidence with no metadata at all — the wrong epoch usually throws a date into the seventeenth century, which is a strong enough signal on its own.