What Unix time is
A Unix timestamp is the number of seconds since the “Unix epoch”: 1 January 1970, 00:00:00 UTC. For example, 1800000000 is 15 January 2027, 08:00:00 UTC. The format is easy to compare and store, and the time zone is applied only when the value is displayed.
Seconds or milliseconds
- 10 digits — seconds: Unix, PHP
time(), Pythontime.time(), PostgreSQLextract(epoch …). - 13 digits — milliseconds: JavaScript
Date.now(), JavaSystem.currentTimeMillis().
A common bug is passing milliseconds where seconds are expected: the date jumps tens of thousands of years into the future.
Converting a timestamp to a date in code
| Language | Seconds → date |
|---|---|
| JavaScript | new Date(ts * 1000).toISOString() |
| Python | datetime.fromtimestamp(ts, tz=timezone.utc) |
| PHP | date('Y-m-d H:i:s', $ts) |
| PostgreSQL | to_timestamp(ts) |
| MySQL | FROM_UNIXTIME(ts) |
| Excel | =A1/86400+DATE(1970,1,1) |
Time zones
A timestamp is always UTC. To show local time, apply the zone’s offset — for example New York is UTC−5 in winter and UTC−4 in summer, London is UTC+0 / UTC+1, Dubai is UTC+4 all year. Store timestamps (or times marked as UTC) in the database and convert to local time in the user interface.
The year 2038 problem
Older systems store time in a signed 32-bit integer. Its maximum, 2,147,483,647, corresponds to 19 January 2038, 03:14:07 UTC; after that the counter overflows. The fix is a 64-bit type, which modern operating systems and languages already use.
Frequently asked questions
What is a Unix timestamp?
It is the number of seconds that have passed since 00:00:00 UTC on 1 January 1970. It does not store a time zone, so the same number means the same moment anywhere in the world.
Why do some timestamps have 10 digits and others 13?
10 digits are seconds (the usual unit in Unix, PHP, Python and databases). 13 digits are milliseconds, returned by JavaScript Date.now() and many APIs.
How do I convert a timestamp to a date in Excel?
For seconds use =A1/86400+DATE(1970,1,1) and format the cell as date and time. The result is in UTC; add or subtract your offset in hours divided by 24 to get local time.
What is the year 2038 problem?
If time is stored in a signed 32-bit integer, the maximum is 2,147,483,647 seconds — 19 January 2038, 03:14:07 UTC. After that the value overflows. Modern systems use 64-bit time.