Epoch to Timestamp: Simple Conversions for Developers and Analysts

Introduction

Converting epoch to timestamp sounds simple, yet it’s one of the most common sources of time bugs. Whether you’re parsing logs, debugging APIs, or building reports, knowing how to move from epoch to timestamp (and back) saves hours. This guide shows exactly how, with clear steps, examples, and best practices for seconds vs milliseconds and UTC vs local time.

Featured Snippet (Quick Answer)

To convert epoch to timestamp, treat the epoch as seconds or milliseconds since 1970-01-01 00:00:00 UTC. Example: JavaScript seconds → new Date(1700000000 * 1000). Python → datetime.fromtimestamp(1700000000, tz=timezone.utc). Bash → date -u -d @1700000000. For milliseconds, divide by 1000 (or use language-specific milli helpers). Always confirm units and timezone.

AI Overview (Concise Summary)

This guide explains how to convert epoch (Unix time) to human-readable timestamps safely and accurately. You’ll learn unit differences (seconds vs milliseconds), timezone handling (UTC vs local), and platform-specific methods (JavaScript, Python, SQL, Bash, Excel, Java, C#, PHP). It includes real-world examples, common pitfalls (e.g., 2038 problem, DST, leap seconds), best practices, and a comparison table of formats. Use this as your go-to reference for reliable, cross-system time conversions.

Key Takeaways

• Epoch is time since 1970-01-01 00:00:00 UTC (Unix epoch).
• Confirm units before converting: seconds vs milliseconds.
• Convert in UTC to avoid timezone drift; format to local time only for display.
• Use built-in language functions (e.g., Date, Instant, datetime) for safety.
• Favor ISO 8601/RFC 3339 for machine-friendly, unambiguous timestamps.
• Use 64-bit storage to avoid overflow and the 2038 problem.

Table of Contents

• What is epoch to timestamp
• Why it Matters
• Benefits
• Step-by-Step Guide
  • With ZenixTools (No Code)
  • JavaScript/TypeScript
  • Python
  • Bash/Unix CLI
  • SQL (PostgreSQL, MySQL, SQLite)
  • Excel/Google Sheets
  • Java/Kotlin
  • C#/.NET
  • PHP
• Real World Examples
• Common Mistakes
• Best Practices
• Expert Tips
• Comparison Table
• Frequently Asked Questions
• External References
• Internal Link Suggestions (ZenixTools)
• Conclusion
• Call To Action

What is epoch to timestamp

Epoch (Unix time) is a running count of time since 1970-01-01 00:00:00 UTC. A timestamp is a human-readable date-time, often in formats like ISO 8601 (e.g., 2023-11-14T20:53:20Z).

Key points:

• Epoch can be in seconds (s) or milliseconds (ms). Know which you have.
• Timestamps can be in UTC (Z) or a local timezone with an offset (e.g., -05:00).
• Many systems store epoch for speed and space, but display ISO 8601 for clarity.

Terminology:

• Unix epoch: The fixed start point, 1970-01-01 00:00:00 UTC.
• Unix timestamp (numeric): Seconds or milliseconds since the epoch.
• ISO 8601 timestamp: 2024-01-29T15:00:00Z (machine-readable, includes timezone info).

Why seconds vs milliseconds matters:

• 1700000000 could be 2001-09-13 01:46:40Z (seconds) or 1970-01-20 if misread as ms.
• 1700000000000 (13 digits) is likely milliseconds; 10 digits is likely seconds.

Why it Matters

Time is the backbone of logging, analytics, security, and billing. A one-hour offset can break SLAs, confuse users, and misalign reports. Converting epoch to timestamp correctly ensures:

• Consistent logs across services and regions.
• Accurate analytics windows and user sessions.
• Reliable audits, incident timelines, and billing windows.
• Correct scheduling, expiration, and token validation.

Benefits

• Faster debugging: Instantly see human times from raw logs.
• Less guesswork: Unit-aware conversions reduce errors.
• Data hygiene: Store precise times and display them correctly.
• Cross-language consistency: Shared patterns and formats.
• Automation-ready: Works in code, CLI, and spreadsheets.

Step-by-Step Guide

With ZenixTools (No Code)

1. Open ZenixTools → Epoch Converter.
2. Paste your epoch value.
3. Select units: seconds or milliseconds.
4. Choose output: ISO 8601 (UTC), local time, or custom format.
5. Copy the result or download in CSV/JSON.

Notes:

• Use the “Auto-detect units” toggle if unsure about s vs ms.
• Switch “Zulu/UTC” on to lock in UTC.
• Batch convert multiple values via the bulk tab.

JavaScript/TypeScript

• Epoch seconds to Date:

const s = 1700000000; // seconds
const d = new Date(s * 1000);
console.log(d.toISOString()); // 2023-11-14T20:53:20.000Z

• Epoch milliseconds to Date:

const ms = 1700000000000; // milliseconds
const d = new Date(ms);
console.log(d.toISOString());

• Local time display:

console.log(d.toLocaleString()); // Uses user’s locale/timezone

• Date to epoch:

const nowMs = Date.now();
const nowS = Math.floor(Date.now() / 1000);

Tips:

• Always format in UTC for logs: d.toISOString().
• For libraries: use Temporal (when available), Luxon, or Day.js for complex formatting.

Python

• Epoch seconds to datetime (UTC):

from datetime import datetime, timezone
s = 1700000000
dt = datetime.fromtimestamp(s, tz=timezone.utc)
print(dt.isoformat())  # 2023-11-14T20:53:20+00:00

• Epoch milliseconds to datetime:

ms = 1700000000000
dt = datetime.fromtimestamp(ms / 1000, tz=timezone.utc)

• Local time conversion:

local_dt = dt.astimezone()  # converts UTC -> local timezone

• Datetime to epoch:

now_s = int(datetime.now(tz=timezone.utc).timestamp())
now_ms = int(datetime.now(tz=timezone.utc).timestamp() * 1000)

Bash/Unix CLI

• Epoch seconds (UTC):

date -u -d @1700000000  # GNU date

• macOS/BSD (use gdate via coreutils):

gdate -u -d @1700000000

• Custom format:

date -u -d @1700000000 "+%Y-%m-%dT%H:%M:%SZ"

• Milliseconds:

s=$((1700000000000/1000)); date -u -d @"$s"

SQL (PostgreSQL, MySQL, SQLite)

• PostgreSQL:

SELECT to_timestamp(1700000000) AT TIME ZONE 'UTC'; -- 2023-11-14 20:53:20+00
SELECT EXTRACT(EPOCH FROM NOW()); -- current epoch seconds
-- milliseconds
offset
SELECT to_timestamp(1700000000000 / 1000.0) AT TIME ZONE 'UTC';

• MySQL/MariaDB:

SELECT FROM_UNIXTIME(1700000000); -- local session tz
SELECT CONVERT_TZ(FROM_UNIXTIME(1700000000), '+00:00', '+00:00'); -- force UTC
-- milliseconds
SELECT FROM_UNIXTIME(1700000000000 / 1000);

• SQLite:

SELECT datetime(1700000000, 'unixepoch'); -- UTC
SELECT datetime(1700000000000/1000, 'unixepoch'); -- ms

Excel/Google Sheets

Excel stores dates as days since 1899-12-30. Convert epoch seconds:

• Seconds to Excel date:

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

• Force UTC display: set cell to custom format: yyyy-mm-ddThh:mm:ssZ (note: Excel shows local time; consider offset if needed).
• Milliseconds:

= (A2 / 1000) / 86400 + DATE(1970,1,1)

• Google Sheets (similar):

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

Java/Kotlin

• Epoch seconds/milliseconds (UTC):

import java.time.*;
Instant i1 = Instant.ofEpochSecond(1700000000L);
Instant i2 = Instant.ofEpochMilli(1700000000000L);
ZonedDateTime z = i1.atZone(ZoneOffset.UTC); // 2023-11-14T20:53:20Z

• Format ISO 8601:

DateTimeFormatter ISO = DateTimeFormatter.ISO_INSTANT;
String iso = ISO.format(i1);

C#/.NET

var i = DateTimeOffset.FromUnixTimeSeconds(1700000000);
string iso = i.UtcDateTime.ToString("o"); // 2023-11-14T20:53:20.0000000Z
// milliseconds
var m = DateTimeOffset.FromUnixTimeMilliseconds(1700000000000);

PHP

$dt = (new DateTimeImmutable('@1700000000'))->setTimezone(new DateTimeZone('UTC'));
echo $dt->format(DateTime::ATOM); // 2023-11-14T20:53:20+00:00
// milliseconds
$ms = 1700000000000;
$dtMs = (new DateTimeImmutable('@' . intval($ms/1000)))->setTimezone(new DateTimeZone('UTC'));

Real World Examples

• API payloads: Many services return epoch in seconds (e.g., Stripe event.created). Convert to ISO 8601 for logs and dashboards.
• JWT tokens: iat and exp are epoch seconds. Validate by comparing to current epoch (use UTC) and watch for clock skew.
• Log aggregation: Nginx/Apache logs often contain epoch. Convert to UTC ISO for consistent cross-region analysis.
• Analytics exports: CSVs might have mixed units. Auto-detect and normalize to seconds before processing.
• Mobile apps: iOS uses seconds; Android often uses milliseconds. Normalize at API boundaries.
• Databases: Store as BIGINT (epoch) for speed, or as TIMESTAMP WITH TIME ZONE for clarity; index whichever you query most.
• Scheduling: Cron outputs in local time; convert input/output to UTC to avoid DST surprises.

Example JSON transformation (JavaScript):

const evt = { created: 1700000000, user: "123", action: "login" };
const createdIso = new Date(evt.created * 1000).toISOString();
// { createdIso: "2023-11-14T20:53:20.000Z", ... }

Common Mistakes

• Mixing units: Treating milliseconds as seconds (or vice versa).
• Ignoring timezone: Converting to local, then assuming it’s UTC.
• Rounding wrong: Using Math.round when you need Math.floor (future drift).
• Locale formatting: Parsing user-facing strings that omit timezone.
• 2038 problem: 32-bit epoch overflow for time_t; use 64-bit types.
• DST confusion: Scheduling jobs in local time around DST changes.
• Leap seconds: Most libraries ignore them; assume continuous Unix time.
• Integer overflow: Storing milliseconds in 32-bit integers.

Best Practices

• Store in UTC; display in user’s local timezone only at the UI layer.
• Document units clearly in APIs and schemas (seconds, milliseconds, microseconds).
• Prefer ISO 8601/RFC 3339 strings for interoperability.
• Use 64-bit integers for epoch, especially when using milliseconds.
• Validate ranges and sanity-check inputs (10 vs 13 digits, plausible year).
• In SQL, use built-in functions (to_timestamp, FROM_UNIXTIME) instead of manual math where available.
• Keep timezone data updated (IANA tz database) to reflect policy changes.
• Include time offsets in logs when not using UTC (e.g., -05:00).

Expert Tips

• Performance: Formatting time for every row can be expensive; precompute display strings for hot dashboards.
• Indexing: If you filter by time a lot, index the epoch or TIMESTAMP columns accordingly.
• Precision: Need sub-ms? Consider microseconds/nanoseconds types (e.g., PostgreSQL timestamp(6)/Instant).
• Testing: Freeze time in tests (e.g., Java Clock.fixed, Python freezegun) to avoid flaky assertions.
• Conversion boundaries: Convert epochs to timestamps as late as possible in pipelines to keep precision.
• Error handling: Reject ambiguous inputs; require explicit unit selection in forms.

Comparison Table

Frequently Asked Questions

1. What is the Unix epoch?

• It’s the start point 1970-01-01 00:00:00 UTC used to count time in seconds or milliseconds.

2. What’s the difference between epoch and timestamp?

• Epoch is numeric seconds or milliseconds since 1970-01-01 UTC. A timestamp is a readable date-time string like 2024-01-29T15:00:00Z.

3. Is my value in seconds or milliseconds?

• Check digits: 10 digits ≈ seconds, 13 digits ≈ milliseconds. Or compare to now: Date.now() in JS returns ms.

4. How do I convert epoch to timestamp in JavaScript?

• Seconds: new Date(s * 1000).toISOString(). Milliseconds: new Date(ms).toISOString().

5. Why is my result off by hours?

• You likely converted in local time or formatted with a local timezone. Use UTC for consistent results.

6. Does daylight saving time change the epoch value?

• No. Epoch is UTC-based and continuous. DST only affects how local times display.

7. What about the 2038 problem?

• 32-bit signed seconds overflow around 2038-01-19. Use 64-bit integers or modern time libraries.

8. Can epoch be negative?

• Yes, for dates before 1970-01-01 UTC. Many libraries handle this, but test it.

9. How do I convert in PostgreSQL?

• Use to_timestamp(seconds) AT TIME ZONE 'UTC'. For ms: to_timestamp(ms/1000.0).

10. How do I convert in MySQL?

• Use FROM_UNIXTIME(seconds). To force UTC, wrap with CONVERT_TZ(..., '+00:00', '+00:00'). Divide ms by 1000.

11. How do I handle microseconds or nanoseconds?

• Divide appropriately (e.g., ns / 1e9). Use types/libraries that support higher precision (Instant, timestamp(6)).

12. Are ISO 8601 and RFC 3339 the same?

• RFC 3339 is a widely used profile of ISO 8601, commonly used in web APIs.

13. How do I convert in Excel?

• =(A2/86400) + DATE(1970,1,1). Set a proper format. Remember Excel displays local time.

14. Should I store epoch or ISO 8601?

• Store whichever fits your workload. Epoch is compact/fast; ISO 8601 is clear and portable. Many systems store epoch and expose ISO.

15. How do I validate epoch inputs in APIs?

• Enforce numeric types, check digit length (10/13), range-check against plausible dates, and document units explicitly.

External References

• MDN Web Docs: Date and time in JavaScript — https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
• Python datetime — https://docs.python.org/3/library/datetime.html
• PostgreSQL to_timestamp — https://www.postgresql.org/docs/current/functions-datetime.html
• MySQL FROM_UNIXTIME — https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime
• SQLite date/time functions — https://www.sqlite.org/lang_datefunc.html
• IANA Time Zone Database — https://www.iana.org/time-zones
• W3C Date and Time Formats / ISO 8601 — https://www.w3.org/TR/NOTE-datetime
• Google Search Central: Dates in structured data (ISO 8601) — https://developers.google.com/search/docs/appearance/structured-data/article#datepublished-and-datemodified
• Schema.org: datePublished/dateModified — https://schema.org/Article

Internal Link Suggestions (ZenixTools)

• ZenixTools Unix Timestamp Converter
• ZenixTools ISO 8601 Date Formatter
• ZenixTools Time Zone Converter (UTC ↔ Local)
• ZenixTools JWT Decoder & Timestamp Inspector
• ZenixTools Cron Expression Parser & Scheduler

Conclusion

Converting epoch to timestamp is easy once you confirm two things: units (seconds vs milliseconds) and timezone (use UTC for consistency). With the right functions across JS, Python, SQL, Bash, and spreadsheets, you’ll avoid common pitfalls, keep data clean, and make timelines trustworthy. Bookmark this guide as your practical reference for epoch to timestamp conversions.

Call To Action

Ready to convert faster and safer? Open ZenixTools’ Epoch Converter to auto-detect units, lock UTC, and batch-convert logs in seconds. Get accurate results, every time—start converting epoch to timestamp now.