How to Convert Timestamps (JS, Python, SQL & More)

Converting between a Unix timestamp and a human-readable date is one of those everyday tasks that seems trivial until a time zone, a milliseconds-versus-seconds mix-up, or a database quirk turns it into a debugging session. This guide is a practical, example-driven reference for converting timestamps in every direction and in the tools you actually use — JavaScript, Python, SQL, the command line and spreadsheets.

It is written for developers and engineers who convert dates regularly, students learning the mechanics, and anyone who wants a reliable recipe rather than guesswork.

The Two Directions (and the Two Units)

Every timestamp conversion is one of two operations, complicated by one recurring question. The operations are timestamp → human date (decoding a stored number for display) and human date → timestamp (encoding a date for storage or comparison). The recurring question, which you must answer every single time, is whether the timestamp is in seconds or milliseconds.

As a rule of thumb for the current era, a ten-digit number (around 1.7 billion) is seconds, and a thirteen-digit number (around 1.7 trillion) is milliseconds. Get this wrong and your date will be off by a factor of a thousand — either fifty thousand years in the future or right back at 1970. Before any conversion, glance at the magnitude and confirm the unit. The other thing to keep straight is the time zone: a Unix timestamp corresponds to a UTC instant, so decide deliberately whether you want to display it in UTC or in a local zone.

In short: To convert a Unix timestamp to a date, multiply seconds by 1000 for millisecond-based APIs (or pass seconds directly to second-based ones) and format with a date library, choosing UTC or local explicitly. To go the other way, build a date with an explicit time zone and read its timestamp.

Understanding What Conversion Really Does

Before diving into specific languages, it helps to understand what is actually happening in a timestamp conversion, because the mental model makes every tool's syntax feel obvious rather than arbitrary. A Unix timestamp names a single, absolute instant — a specific point on the timeline of the universe — with no reference to any calendar or clock. "Converting to a human date" does not change that instant; it simply describes it in calendar terms that a person can read, by working out which year, month, day, hour, minute and second that instant corresponds to in a chosen time zone. The reverse conversion does the opposite: it takes a calendar description plus a time zone and works back to the single absolute instant they refer to.

This is why the time zone is not an optional extra but a core part of the conversion. The same instant is "10pm on the 14th" in London and "5pm on the 14th" in New York; both descriptions name the identical timestamp. So when you convert a timestamp to a date, you are really answering the question "how would a clock in this particular zone have read at that instant?" — and you must decide which zone you mean. Once you internalise that a timestamp is an absolute instant and a human date is a zone-specific description of it, the whole topic clicks into place: conversion is just translation between an absolute number and a human description, and the time zone is the dictionary you translate with.

Choosing Between UTC and Local Display

A recurring decision in conversion is whether to present a time in UTC or in a local zone, and the right answer depends on who is reading it and why. UTC is the correct choice for anything that machines or distributed teams consume: log files, audit trails, data exchanged between services, and timestamps stored for later comparison. Because UTC is unambiguous and has no daylight saving, a UTC time means the same thing to a server in Frankfurt, a developer in Mumbai and a log aggregator in Virginia, which is precisely what you want when correlating events across systems.

Local time, by contrast, is for end users reading a screen. A person booking a meeting or checking when their order shipped wants to see the time on their clock, in their own zone, with daylight saving applied. The disciplined pattern, therefore, is to keep everything in UTC right up until the final moment of display, and to convert to the user's local zone only in the presentation layer — ideally using their device's detected zone or an explicit preference. Mixing the two, such as storing local times or logging in local zones, is how systems end up with data that cannot be reliably compared and bugs that only appear for users in certain regions. Decide deliberately, default to UTC everywhere internal, and reserve local conversion for the human-facing edge.

JavaScript

JavaScript's Date works in milliseconds, so seconds-based timestamps must be multiplied by 1000.

// Timestamp (seconds) -> date
const ts = 1700000000;
const d = new Date(ts * 1000);

d.toISOString();        // "2023-11-14T22:13:20.000Z"  (UTC)
d.toString();           // local time, with the runtime's zone
d.toLocaleString("en-US", { timeZone: "America/New_York" }); // a specific zone

// Date -> timestamp (seconds)
Math.floor(Date.now() / 1000);                 // now, in seconds
Math.floor(new Date("2023-11-14T22:13:20Z").getTime() / 1000); // 1700000000

Use toISOString() when you want a UTC, machine-friendly string, and toLocaleString with an explicit timeZone when you want a specific local presentation.

Python

Python's time.time() and datetime.timestamp() work in seconds. Always pass a time zone to avoid ambiguity.

from datetime import datetime, timezone

# Timestamp -> date (UTC)
datetime.fromtimestamp(1700000000, tz=timezone.utc).isoformat()
# "2023-11-14T22:13:20+00:00"

# Date -> timestamp
int(datetime(2023, 11, 14, 22, 13, 20, tzinfo=timezone.utc).timestamp())
# 1700000000

# Current timestamp
import time
int(time.time())

Avoid the naive datetime.fromtimestamp(ts) without a tz argument unless you specifically want the server's local zone, because it silently uses local time and is a frequent source of confusion.

SQL

Databases each have their own helpers, but the idea is the same. A few common examples:

-- PostgreSQL: timestamp (seconds) -> timestamptz
SELECT to_timestamp(1700000000);

-- PostgreSQL: date -> Unix seconds
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2023-11-14 22:13:20Z')::bigint;

-- MySQL: seconds -> datetime (UTC), and back
SELECT FROM_UNIXTIME(1700000000);
SELECT UNIX_TIMESTAMP('2023-11-14 22:13:20');

Be aware of each database's time-zone settings: some functions return values in the session or server time zone rather than UTC, which can surprise you.

Command Line and Spreadsheets

# Unix/Linux date: seconds -> human (UTC)
date -u -d @1700000000        # GNU date
# macOS (BSD date)
date -u -r 1700000000

In spreadsheets, dates are stored as a day count from a different epoch (often 1899/1900), so converting a Unix timestamp requires arithmetic:

// Excel / Google Sheets: Unix seconds in A1 -> date
= A1 / 86400 + DATE(1970, 1, 1)
// then format the cell as a date/time

The 86400 converts seconds to days, and adding the 1970 date shifts from the Unix epoch to the spreadsheet's epoch.

Try Our Free Timestamp Converter

When you just need the answer without remembering each language's quirks, our Timestamp Converter does it instantly.

• ✅ Unix timestamp ↔ human date, both directions
• ✅ Handles seconds and milliseconds
• ✅ Shows UTC clearly; runs entirely in your browser

👉 Convert a timestamp now →

A Reliable Conversion Workflow

When you put the pieces together, a dependable routine emerges that works regardless of language or tool. It begins with identifying the unit of the value you have — a glance at the digit count settles whether it is seconds, milliseconds or something finer. Next you decide the direction you need: are you decoding a stored number into a readable date, or encoding a known date into a number for storage and comparison? Then you choose the target zone deliberately, defaulting to UTC unless you are producing something for a human to read in their own locale. Only after those three decisions do you reach for the actual conversion function, at which point the syntax is just a detail. Finally, you sanity-check the result, confirming that a current value lands in the present and that a known historical date comes back as the number you expected.

This workflow matters because conversion bugs are rarely failures of the conversion function itself — the libraries are correct — but failures to answer those preliminary questions. A factor-of-1000 error is a unit question answered wrong; an off-by-hours error is a zone question answered wrong or skipped; a date that comes out in the wrong era is usually a unit or epoch mismatch. By making the unit, direction and zone explicit choices rather than assumptions, you eliminate the conditions in which these bugs arise. The conversion code becomes trivial precisely because you did the thinking before you wrote it, and a quick check with a converter at the end confirms you got the thinking right.

A Word on ISO 8601

When you convert a timestamp to a string for storage or exchange, prefer ISO 8601 — the format 2023-11-14T22:13:20Z. It is unambiguous, sorts correctly as text, is parseable by every modern language, and carries its time zone explicitly (the Z for UTC, or an offset like +05:30). Avoid locale-specific formats like 11/14/2023 for anything machines will read, because 03/04/2023 could be March or April depending on the reader's locale. Keep integers for computation and ISO 8601 for human-readable interchange, and you sidestep a whole class of parsing problems.

Common Mistakes

1. Seconds/milliseconds mix-ups. The classic factor-of-1000 error — confirm the unit before converting.
2. Ignoring the time zone. Converting to "a date" without deciding UTC versus local produces off-by-hours results.
3. Using naive local conversions. Functions that default to the server's zone behave differently across machines.
4. Parsing ambiguous date strings. Locale formats are unreliable; use ISO 8601.
5. Forgetting the spreadsheet epoch. Spreadsheets count days from ~1900, not seconds from 1970.

Common Conversion Scenarios

It helps to ground the mechanics in the situations where they actually come up, because the right approach shifts a little with the context. The most frequent scenario is displaying a stored timestamp to a user: you have a number in your database and you need to show a friendly date on a page. Here you decode the timestamp and format it in the user's local time zone, ideally one detected from their browser or saved in their preferences, so the time reads naturally to them. The opposite scenario is capturing a time a user entered — a deadline, a booking — where you take their local input, attach their zone, and convert to a UTC timestamp for storage, so the stored value is unambiguous regardless of who reads it later.

A third common scenario is moving timestamps between systems, such as receiving a value from a third-party API. The crucial first step is to find out, from the documentation or by inspection, what unit and zone that system uses, because external systems vary and an unverified assumption is the usual cause of conversion bugs. A fourth is analytical work — bucketing events by hour or day, or computing durations — which is almost always best done in UTC, where there are no daylight-saving discontinuities to distort the buckets, with any local-time presentation applied only to the final report. Recognising which of these scenarios you are in tells you immediately whether to lean toward local or UTC and which direction to convert, turning a potentially fiddly task into a routine one.

Across all of these, the safeguard that catches mistakes early is a quick sanity check on the result. A converted "now" should read as the present moment; a known reference date should round-trip back to the number you started with; a user-entered local time should, when converted to UTC and back to their zone, return the time they typed. Building these tiny verifications into your workflow — or simply pasting a value into a converter to confirm it — costs seconds and saves the far longer debugging sessions that follow when a unit or zone assumption slips through unnoticed.

Best Practices

• Confirm the unit (seconds vs ms) first, every time.
• Always specify the time zone explicitly — convert to UTC by default and to local only for display.
• Use ISO 8601 for string representations.
• Store integers (or native timestamp types), not formatted strings.
• Rely on date libraries rather than manual offset arithmetic.
• Sanity-check the result — a current timestamp should land in the present, not 1970 or the far future.

Frequently Asked Questions

How do I convert a Unix timestamp to a date?

Pass it to your language's date function — for example new Date(ts * 1000) in JavaScript (which uses milliseconds) or datetime.fromtimestamp(ts, tz=timezone.utc) in Python (which uses seconds) — and format it, choosing UTC or local explicitly. An online converter does it instantly.

How do I convert a date to a Unix timestamp?

Build the date with an explicit time zone and read its epoch value: Math.floor(Date.parse("2023-11-14T22:13:20Z")/1000) in JavaScript, or int(datetime(...).timestamp()) with a tzinfo in Python.

How do I know if a timestamp is in seconds or milliseconds?

Check the magnitude: a current seconds timestamp has about 10 digits (around 1.7 billion), and a milliseconds one has about 13 digits (around 1.7 trillion).

How do I convert a Unix timestamp in Excel?

Use = A1/86400 + DATE(1970,1,1) where A1 holds the seconds value, then format the cell as a date. The division converts seconds to days and the addition shifts to the spreadsheet's epoch.

Why is my converted date in the wrong time zone?

Because the conversion used local time implicitly. Always specify the time zone — convert to UTC explicitly, then to a specific local zone only when displaying.

What date format should I use for storage?

Store a Unix integer or a native timestamp type for computation, and use ISO 8601 (such as 2023-11-14T22:13:20Z) for human-readable or interoperable string representations.

Summary

Timestamp conversion comes down to a short, reliable routine: confirm whether the value is seconds or milliseconds, decide whether you want UTC or a local zone, then use your language's date library to convert in the appropriate direction. JavaScript works in milliseconds, Python and most databases in seconds, and spreadsheets count days from a different epoch entirely — but the underlying logic never changes. Favour ISO 8601 for strings, store integers for computation, specify time zones explicitly, and sanity-check that the result lands where you expect. Internalise that recipe, keep a converter handy for quick checks, and timestamp conversions stop being a source of surprises.

Conversion only feels difficult while the unit and time-zone questions remain implicit. Make them explicit, lean on your language's date library for the mechanics, and verify the result against a known-good reference, and what once felt like a fiddly chore becomes a quick, dependable step you barely think about. The recipes in this guide cover the tools you are most likely to reach for, but the underlying discipline — know the unit, choose the zone, convert, verify — carries over to any language or platform you encounter next.

👉 Convert timestamps instantly with our free tool →

Related Resources

• Unix Timestamp Explained — the fundamentals
• Unix Time vs UTC — the time-zone picture
• Timestamp Debugging Guide — fix conversion bugs
• Timestamp Converter — the tool