Unix time in one sentence
Unix time, also called epoch time or POSIX time, is a single integer that counts seconds from the epoch, 1970-01-01T00:00:00Z. It has no time zone, no daylight saving and no calendar inside it, which is exactly why databases, log files, APIs, JWT tokens and file systems use it: two servers on different continents write the same number for the same moment.
Dates before 1970 are negative numbers: -1 is 31 December 1969 at 23:59:59 UTC. The current value is a little under two billion, and it grows by 86,400 every day.
Try it here: Unix Timestamp Converter
Open the full tool pageSeconds, milliseconds, microseconds
The definition uses seconds, but many systems store finer units and still call it a Unix timestamp. The quickest way to tell them apart in 2026 is the number of digits, which is also how the converter on this page detects the unit.
Mixing them up is the most common bug: treat a millisecond value as seconds and you get a date about 54,000 years in the future; treat seconds as milliseconds and you land in January 1970. If a converted date shows 1970, multiply by 1,000. If it shows a year far in the future, divide by 1,000.
| Unit | Digits today | Example for the same moment | Typical sources |
|---|---|---|---|
| Seconds | 10 | 1700000000 | Unix and Linux, PHP time(), MySQL UNIX_TIMESTAMP(), JWT exp and iat |
| Milliseconds | 13 | 1700000000000 | JavaScript Date.now(), Java System.currentTimeMillis(), many JSON APIs |
| Microseconds | 16 | 1700000000000000 | PostgreSQL internals, some Python and database tools |
| Nanoseconds | 19 | 1700000000000000000 | Go time.UnixNano(), high resolution tracing |
Time zones and UTC
A timestamp itself is always UTC based; time zones only appear when you turn it into a calendar date for a person to read. The same value 1700000000 is 22:13 in London, 17:13 in New York and 01:13 the next day in Istanbul. Nothing about the number changes, only the display.
That is why the best practice is to store timestamps (or UTC dates) and convert to local time at the very last step, in the user interface. Storing local times leads to duplicate or missing hours when clocks change for daylight saving. The converter shows each timestamp in your local time, in UTC and in one extra zone you choose under Also show in, plus ISO 8601 (2023-11-14T22:13:20.000Z) for APIs and RFC 2822 for email headers.
One detail: Unix time ignores leap seconds. Every day is exactly 86,400 seconds, so a timestamp can be converted to a date with simple arithmetic. Leap seconds have been absorbed by the clocks, not counted in the number.
Converting in Excel and Google Sheets
Spreadsheets count days since 1900 instead of seconds since 1970, so you divide by the number of seconds in a day and add the epoch date. Format the result cell as Date or Date time, otherwise you will see a decimal number like 45244.93.
The result is in UTC. To show local time, add the offset in hours divided by 24, for example +3/24 for Istanbul or -5/24 for New York in winter. Excel does not know about daylight saving, so a fixed offset is wrong for half of the year in many countries; for large logs convert with the bulk mode of the converter instead.
| Task | Formula (timestamp in A2) |
|---|---|
| Seconds to date (UTC) | =A2/86400+DATE(1970,1,1) |
| Milliseconds to date (UTC) | =A2/86400000+DATE(1970,1,1) |
| Date in A2 to seconds | =(A2-DATE(1970,1,1))*86400 |
| Seconds to Istanbul time (UTC+3) | =A2/86400+DATE(1970,1,1)+3/24 |
Converting in JavaScript, Python and SQL
Every major language and database has built in functions. Watch the unit each one expects: JavaScript works in milliseconds, most others in seconds.
| Environment | Current timestamp | Timestamp to date |
|---|---|---|
| JavaScript | Math.floor(Date.now() / 1000) | new Date(ts * 1000).toISOString() |
| Python | int(time.time()) | datetime.fromtimestamp(ts, tz=timezone.utc) |
| PHP | time() | gmdate('Y-m-d H:i:s', $ts) |
| MySQL | UNIX_TIMESTAMP() | FROM_UNIXTIME(ts) |
| PostgreSQL | extract(epoch from now()) | to_timestamp(ts) |
| SQL Server | DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME()) | DATEADD(SECOND, ts, '1970-01-01') |
| SQLite | strftime('%s', 'now') | datetime(ts, 'unixepoch') |
The year 2038 problem
Many older systems store Unix time as a signed 32 bit integer. Its largest value is 2,147,483,647, which is reached on 19 January 2038 at 03:14:07 UTC. One second later the number overflows to a large negative value and the date jumps back to December 1901. Anything that calculates dates into the future, such as 20 year loans, certificates or scheduled jobs, can hit it earlier.
Modern operating systems, languages and databases use 64 bit time, which lasts for billions of years. The risk is in older embedded devices, file formats and database columns. In MySQL, for example, the TIMESTAMP column type still ends in January 2038, so long lived data is safer in DATETIME or BIGINT columns. If you maintain old code, search for 32 bit time fields and int columns that hold timestamps.
Useful reference values
- 0 = 1970-01-01 00:00:00 UTC, the epoch itself.
- 1000000000 = 2001-09-09 01:46:40 UTC, the first ten digit timestamp.
- 1700000000 = 2023-11-14 22:13:20 UTC.
- 2000000000 = 2033-05-18 03:33:20 UTC.
- 2147483647 = 2038-01-19 03:14:07 UTC, the 32 bit limit.
- 86400 = one day, 604800 = one week, 3600 = one hour.