Unix Time to Date: Convert Epoch Timestamps Without Timezone Bugs

Quick answer

Unix timestamp 1700000000 is:

2023-11-14T22:13:20Z
Tuesday, November 14, 2023 at 22:13:20 UTC

The conversion has two steps:

1. Decide the unit: seconds, milliseconds, microseconds, or nanoseconds.
2. Decode the value as one UTC instant, then format that instant for the timezone you need.

For the same example:

new Date(1700000000 * 1000).toISOString();
// "2023-11-14T22:13:20.000Z"

That * 1000 is the detail that prevents most "why is my date in 1970?" bugs.

First, identify the timestamp unit

Do this before writing any conversion code. The number of digits is not a formal standard, but it is a good production triage rule for modern timestamps.

Example value	Likely unit	Meaning	First check
1700000000	seconds	Unix seconds	convert directly in Python, PHP, SQL, shell
1700000000000	milliseconds	Unix ms / JavaScript time	divide by 1000 for seconds-based APIs
1700000000000000	microseconds	database or event-stream precision	divide by 1,000,000 or use microsecond helpers
1700000000000000000	nanoseconds	Go, tracing, telemetry, high-precision logs	keep as integer/BigInt; do not store in JS Number

The date is wrong when the unit is wrong:

new Date(1700000000).toISOString();
// "1970-01-20T16:13:20.000Z"  <-- seconds accidentally treated as ms

new Date(1700000000 * 1000).toISOString();
// "2023-11-14T22:13:20.000Z"

Two cautions make this rule safer:

• Old 9-digit values can still be valid Unix seconds before 2001.
• Future 11-digit values can also be valid Unix seconds after 2286.

So count digits as a warning system, not as your only validation. When the data comes from an API, database column, webhook, JWT, file export, or analytics event, check that source's documentation and name the field clearly: created_at_seconds, createdAtMs, event_time_micros, not just timestamp.

Use this decision table

Task	Recommended conversion
Check one value quickly	Paste it into the epoch-to-date converter
JavaScript Unix seconds	new Date(seconds * 1000)
JavaScript Unix milliseconds	new Date(milliseconds)
Python UTC datetime	datetime.fromtimestamp(seconds, tz=timezone.utc)
Linux UTC output	date -u -d @seconds
macOS UTC output	date -u -r seconds
PostgreSQL	to_timestamp(seconds)
MySQL	FROM_UNIXTIME(seconds) with session timezone checked
SQLite	datetime(seconds, 'unixepoch')
BigQuery seconds/ms/us	TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS
Excel	=A1/86400 + DATE(1970,1,1)
Google Sheets	=EPOCHTODATE(A1, 1) for seconds

If you are debugging a production issue, convert to UTC first. Once the UTC instant is correct, format that same instant in the user's timezone.

What "Unix time to date" really means

Unix time is a count from 1970-01-01 00:00:00 UTC. It does not store "New York time", "server time", or "browser time". It stores one point on the timeline.

The readable date is a rendering choice:

Same instant	Display
UTC	2023-11-14 22:13:20 UTC
New York	2023-11-14 17:13:20 EST
Tokyo	2023-11-15 07:13:20 JST

That is why two people can convert the same Unix timestamp and see different calendar dates. The timestamp is not changing. The timezone used for display is changing.

The practical rule:

• Store and compare instants in UTC.
• Display local time only at the presentation layer.
• Store an IANA timezone name when the user's local context matters.

Convert Unix time to date in JavaScript

JavaScript's Date constructor accepts milliseconds since the Unix epoch. That is why Unix seconds need * 1000.

const seconds = 1700000000;

const date = new Date(seconds * 1000);

date.toISOString();
// "2023-11-14T22:13:20.000Z"

For a 13-digit millisecond value, do not multiply again:

const milliseconds = 1700000000000;

new Date(milliseconds).toISOString();
// "2023-11-14T22:13:20.000Z"

For user-facing output, pass an explicit timezone. Without timeZone, JavaScript uses the runtime default, which may be your laptop, the user's browser, a Docker container, or a production server.

const formatter = new Intl.DateTimeFormat("en-US", {
  timeZone: "America/New_York",
  dateStyle: "medium",
  timeStyle: "short",
});

formatter.format(new Date(1700000000 * 1000));
// "Nov 14, 2023, 5:13 PM"

A small converter function should reject ambiguous values instead of silently guessing forever:

function unixToDate(value, unit = "seconds") {
  if (!Number.isFinite(value)) {
    throw new TypeError("timestamp must be a finite number");
  }

  if (unit === "seconds") return new Date(value * 1000);
  if (unit === "milliseconds") return new Date(value);
  if (unit === "microseconds") return new Date(Math.trunc(value / 1000));

  throw new RangeError(`Unsupported unit: ${unit}`);
}

unixToDate(1700000000, "seconds").toISOString();
// "2023-11-14T22:13:20.000Z"

Avoid passing null, undefined, or empty strings from form data into new Date():

new Date(null).toISOString();
// "1970-01-01T00:00:00.000Z"

new Date(undefined).toString();
// "Invalid Date"

MDN documents the Date timestamp value as milliseconds since 1970-01-01T00:00:00Z, and local date component constructors are evaluated in the local timezone.

Convert Unix time to date in Python

Use an aware UTC datetime:

from datetime import datetime, timezone

dt = datetime.fromtimestamp(1700000000, tz=timezone.utc)

dt.isoformat()
# '2023-11-14T22:13:20+00:00'

The tz=timezone.utc part is not decoration. Without it, Python returns local time:

datetime.fromtimestamp(1700000000)
# local timezone on the machine running the code

For milliseconds and microseconds:

datetime.fromtimestamp(1700000000000 / 1000, tz=timezone.utc)
datetime.fromtimestamp(1700000000000000 / 1_000_000, tz=timezone.utc)

For Python 3.11 and newer, datetime.UTC is also available:

from datetime import UTC, datetime

datetime.fromtimestamp(1700000000, tz=UTC)

Avoid datetime.utcfromtimestamp() in new code. Python documents it as returning a naive datetime and recommends fromtimestamp(timestamp, timezone.utc) for UTC.

One more edge case: Python may raise range errors for timestamps outside what the platform C time functions support. This matters when you import historical archives, future schedules, or corrupted unit values.

Convert Unix time to date in PHP

PHP's simple UTC formatter is gmdate():

echo gmdate('c', 1700000000);
// 2023-11-14T22:13:20+00:00

date() formats the same timestamp in the server's default timezone:

echo date('Y-m-d H:i:s', 1700000000);
// depends on date.timezone / date_default_timezone_set()

For app code, prefer DateTimeImmutable when you need to format the instant in a named timezone:

$instant = new DateTimeImmutable('@1700000000');

echo $instant
  ->setTimezone(new DateTimeZone('Asia/Tokyo'))
  ->format(DateTimeInterface::ATOM);
// 2023-11-15T07:13:20+09:00

PHP expects Unix seconds in these APIs. If your input is milliseconds, divide by 1000 before using gmdate() or the @timestamp constructor.

Convert Unix time to date in Java

Modern Java code should use java.time.Instant.

Instant instant = Instant.ofEpochSecond(1700000000L);

instant.toString();
// 2023-11-14T22:13:20Z

For milliseconds:

Instant instant = Instant.ofEpochMilli(1700000000000L);

Render the same instant in a timezone only when you need wall-clock output:

ZonedDateTime tokyo = Instant
    .ofEpochSecond(1700000000L)
    .atZone(ZoneId.of("Asia/Tokyo"));

Watch for legacy Java APIs. new java.util.Date(long) expects milliseconds, like JavaScript:

new Date(1700000000L);        // 1970 bug
new Date(1700000000L * 1000); // correct for Unix seconds

Convert Unix time to date in C# and .NET

Use DateTimeOffset for Unix timestamps:

var dto = DateTimeOffset.FromUnixTimeSeconds(1700000000);

dto.ToString("O");
// 2023-11-14T22:13:20.0000000+00:00

For milliseconds:

var dto = DateTimeOffset.FromUnixTimeMilliseconds(1700000000000);

Then convert for display:

var zone = TimeZoneInfo.FindSystemTimeZoneById("America/New_York");
var local = TimeZoneInfo.ConvertTime(dto, zone);

Timezone IDs can be platform-specific in .NET. IANA names such as America/New_York are the natural fit on Linux and macOS; Windows environments may use Windows timezone IDs such as Eastern Standard Time, depending on the runtime and deployment target.

Do not rebuild the epoch by hand with new DateTime(1970, 1, 1).AddSeconds(...) unless you have a compatibility reason. The built-in methods make the unit and UTC baseline obvious.

Convert Unix time to date in Go

Go's time.Unix(sec, nsec) accepts seconds plus nanoseconds:

t := time.Unix(1700000000, 0).UTC()

fmt.Println(t.Format(time.RFC3339))
// 2023-11-14T22:13:20Z

For millisecond and microsecond input, use the dedicated helpers in modern Go:

time.UnixMilli(1700000000000).UTC()
time.UnixMicro(1700000000000000).UTC()

For nanoseconds, split seconds and nanoseconds or use time.Unix(0, ns) when the value is an epoch-nanosecond count:

time.Unix(0, 1700000000000000000).UTC()

Use .In(location) for user-facing timezone output:

loc, _ := time.LoadLocation("Europe/Berlin")
fmt.Println(time.Unix(1700000000, 0).In(loc).Format(time.RFC3339))

Go's documentation notes that its calendrical calculations assume the Gregorian calendar with no leap seconds, which matches the usual Unix timestamp model used by application code.

Convert Unix time to date in shell

On Linux with GNU date:

date -u -d @1700000000
# Tue Nov 14 22:13:20 UTC 2023

date -u -d @1700000000 +'%Y-%m-%dT%H:%M:%SZ'
# 2023-11-14T22:13:20Z

On macOS with BSD date:

date -u -r 1700000000
# Tue Nov 14 22:13:20 UTC 2023

date -u -r 1700000000 +'%Y-%m-%dT%H:%M:%SZ'
# 2023-11-14T22:13:20Z

For local timezone checks, set TZ deliberately:

# GNU date
TZ=America/New_York date -d @1700000000

# BSD date
TZ=America/New_York date -r 1700000000

The common mistake is copying a Linux command into a macOS terminal. GNU uses -d @seconds; BSD uses -r seconds.

Convert Unix time to date in SQL

SQL conversions are easy until timezone display gets involved. Store the instant in a real timestamp type when possible, and check whether the function renders in UTC or in the session timezone.

Database	Unix seconds conversion	Timezone note
PostgreSQL	to_timestamp(1700000000)	returns timestamp with time zone; display follows session timezone
MySQL	FROM_UNIXTIME(1700000000)	returns in current session time zone
SQLite	datetime(1700000000, 'unixepoch')	UTC unless you add localtime
BigQuery	TIMESTAMP_SECONDS(1700000000)	timestamp is UTC-based; display can vary by environment

PostgreSQL:

SELECT to_timestamp(1700000000);
-- 2023-11-14 22:13:20+00, if the session time zone is UTC

SELECT to_timestamp(1700000000) AT TIME ZONE 'UTC';
-- 2023-11-14 22:13:20

MySQL:

SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1700000000);
-- 2023-11-14 22:13:20

SQLite:

SELECT datetime(1700000000, 'unixepoch');
-- 2023-11-14 22:13:20

BigQuery:

SELECT TIMESTAMP_SECONDS(1700000000);
SELECT TIMESTAMP_MILLIS(1700000000000);
SELECT TIMESTAMP_MICROS(1700000000000000);

If you need to preserve the original numeric value for audit or replay, store both the raw integer and the parsed timestamp during migration. That makes bad-unit imports much easier to find.

Convert Unix time to date in Excel

Excel stores dates as serial numbers. To convert Unix seconds, divide by the number of seconds in a day and add Excel's serial value for 1970-01-01 through the DATE function:

=A1/86400 + DATE(1970,1,1)

For other units:

=A1/86400000 + DATE(1970,1,1)      // milliseconds
=A1/86400000000 + DATE(1970,1,1)   // microseconds

Then format the cell as a date/time. If the result still looks like a decimal number, the formula worked; the cell just needs a date format.

Excel-specific cautions:

• Excel for Windows uses the 1900 date system by default.
• Very old dates may not display the way programming languages do.
• Timezone is not attached to the serial number. Label the column as UTC or local.
• CSV imports often turn large timestamps into scientific notation. Format the source column as text before importing if exact values matter.

Convert Unix time to date in Google Sheets

Google Sheets now has a dedicated EPOCHTODATE function:

=EPOCHTODATE(A1, 1)  // seconds
=EPOCHTODATE(A1, 2)  // milliseconds
=EPOCHTODATE(A1, 3)  // microseconds

Google's documentation says the result is UTC, not the spreadsheet's local timezone, and negative timestamps are not accepted.

The Excel-style formula also works for positive Unix seconds:

=A1/86400 + DATE(1970,1,1)

For shared sheets, put the unit and timezone in the header:

created_at_seconds_utc
created_at_ms_utc

That small naming habit prevents the next person from applying the wrong formula to a 13-digit value.

UTC date or local date?

Start with UTC whenever you are unsure. UTC gives every system the same baseline.

Use UTC for:

• server logs
• API payloads
• database exports
• audit trails
• test fixtures
• cross-region dashboards

Use local time for:

• user-facing deadlines
• receipts and invoices
• support tickets
• calendar invitations
• "today" and "yesterday" UI labels

The important part is to label the output:

2023-11-14 22:13:20 UTC
2023-11-14 17:13:20 America/New_York

Do not store only the second string as the source of truth. Store the UTC instant, and store the user's IANA timezone when the local context matters.

Debug common wrong results

Symptom	Likely cause	Fix
Date is in January 1970	seconds passed to millisecond API	multiply seconds by 1000
Date is thousands of years in the future	milliseconds or microseconds passed as seconds	divide by 1000 or 1,000,000
Output differs on laptop and server	runtime default timezone changed	pass UTC or explicit IANA timezone
Python output has no timezone	naive datetime	use tz=timezone.utc
MySQL output shifts by hours	session time zone	set time_zone or convert explicitly
Google Sheets rejects a value	negative timestamp or wrong unit argument	use supported units and test range
CSV lost digits	spreadsheet parsed timestamp as a number	import as text or use integer-safe tooling
Pre-1970 date fails	platform/range limitation	test negative values in that exact tool

The pattern is boring in the best way: most failures are unit, timezone, or range mistakes. Check those three before rewriting date logic.

Edge cases to test

Use a small set of known timestamps in automated tests:

Timestamp	Expected UTC date	Why test it
0	1970-01-01T00:00:00Z	epoch boundary
-1	1969-12-31T23:59:59Z	negative timestamp support
1000000000	2001-09-09T01:46:40Z	10-digit boundary people recognize
1700000000	2023-11-14T22:13:20Z	modern normal case
2147483647	2038-01-19T03:14:07Z	signed 32-bit limit
1700000000000	2023-11-14T22:13:20Z	millisecond input
1700000000000000	2023-11-14T22:13:20Z	microsecond input

For browser or product UI, add one timezone display test as well:

1700000000 in America/New_York = 2023-11-14 17:13:20 EST
1700000000 in Asia/Tokyo       = 2023-11-15 07:13:20 JST

This catches the subtle bug where the UTC conversion is correct but the visible date is formatted in the wrong timezone.

Official references

• MDN Date constructor
• Python datetime.fromtimestamp
• PHP gmdate
• Oracle Java Instant
• Microsoft DateTimeOffset.FromUnixTimeSeconds
• Go package time
• PostgreSQL date/time functions
• MySQL date/time functions
• SQLite date/time functions
• BigQuery timestamp functions
• Google Sheets EPOCHTODATE
• Microsoft Excel DATE function

Related guides

• Date to Epoch
• Current Unix Timestamp
• Epoch Time Explained
• Milliseconds vs Seconds
• JavaScript Date.now()
• Linux date command converter
• Unix timestamps in databases
• ISO 8601 guide