Convert Unix Epoch Time: A Complete, Human-Friendly Guide

Introduction

If you work with logs, APIs, databases, or IoT data, you’ll often need to convert Unix epoch time into a human-readable date (and back again). In this guide, we’ll show fast, accurate ways to convert unix epoch time across tools, operating systems, and programming languages—so you avoid timezone bugs, milliseconds mix-ups, and DST surprises.

Featured Snippet (50–70 words)

To convert Unix epoch time, first identify units: seconds or milliseconds since 1970-01-01 UTC. Examples: JavaScript new Date(1700000000 * 1000) for seconds, new Date(1700000000000) for ms; Python datetime.utcfromtimestamp(1700000000); macOS/Linux date -ud @1700000000; PowerShell DateTimeOffset::FromUnixTimeSeconds(1700000000). Always handle time zones explicitly (UTC recommended) and convert to local time only for display.

AI Overview (under 150 words)

This guide explains what Unix epoch time is and how to convert it to readable dates and timestamps. You’ll learn the difference between seconds and milliseconds, UTC versus local time, and common pitfalls like DST and leap seconds. We provide step-by-step instructions for JavaScript, Python, Java, PHP, Go, SQL dialects, macOS/Linux, Windows PowerShell, Excel, and Google Sheets. You’ll also see real-world examples for logging, analytics, IoT, and scheduling, plus best practices and expert tips to avoid subtle bugs. A quick comparison table helps you choose the right function in each environment.

Key Takeaways

• Epoch time counts seconds (or ms) since 1970-01-01 00:00:00 UTC.
• Always detect units: seconds (10 digits) vs milliseconds (13 digits).
• Convert in UTC first, then format to local time for display.
• Languages and tools vary; use built-in time libraries, not custom math.
• Store as integer epoch seconds for durability; exchange as ISO 8601.
• Beware DST, time zone offsets, and the Year 2038 problem on 32‑bit systems.

Table of Contents

• What Is Unix Epoch Time?
• How to Convert Unix Epoch Time (Overview)
• Why It Matters
• Benefits
• Step-by-Step Guide
• Real World Examples
• Common Mistakes
• Best Practices
• Expert Tips
• Comparison Table
• Frequently Asked Questions
• External References
• Conclusion
• Call To Action

What Is Unix Epoch Time?

Unix epoch time is the number of seconds that have elapsed since 1970-01-01 00:00:00 UTC, not counting leap seconds. Many systems also represent it in milliseconds (thousandths of a second) since the same epoch. It’s a compact, language-agnostic way to record an absolute moment in time.

Key points:

• Epoch origin: 1970-01-01 00:00:00 UTC
• Seconds: typical for databases and APIs
• Milliseconds: common in browsers and some SDKs
• Negative values: times before 1970
• No leap seconds: time is continuous from system perspective

How to Convert Unix Epoch Time (Overview)

To convert unix epoch time:

1. Confirm the unit (seconds or milliseconds).
2. Use a trusted library function for your platform.
3. Treat the result as UTC.
4. Format for display or convert to a target time zone.
5. For round-trip, parse input to epoch seconds and validate.

Examples:

• JavaScript: new Date(SECONDS * 1000) or new Date(MILLISECONDS)
• Python: datetime.utcfromtimestamp(SECONDS)
• PowerShell: DateTimeOffset::FromUnixTimeSeconds(SECONDS)
• SQL: use built-in time functions (varies by database)

Why It Matters

• Interoperability: Epoch time works across languages and systems.
• Performance: Integers are faster to compare and index.
• Storage: Compact and unambiguous in UTC.
• Analytics: Easy to aggregate and window by time.
• Debugging: Log timestamps can be consistently converted.

Benefits

• Clear separation of storage (UTC) and display (local or formatted).
• Avoids locale parsing issues.
• Supports range queries with simple numeric comparisons.
• Works well in distributed and event-driven systems.
• Reduces ambiguity found in free‑form date strings.

Step-by-Step Guide

Below are practical conversions across OS tools, programming languages, and spreadsheets.

1) macOS/Linux Terminal (GNU date)

• From epoch seconds to UTC:
  • date -ud @1700000000
• To local time:
  • date -d @1700000000
• From human-readable to epoch seconds (UTC):
  • date -ud "2023-11-14 00:00:00" +%s

Notes:

• -u uses UTC; -d parses input; the @ prefix tells date to read epoch seconds.

2) Windows PowerShell

• Seconds to DateTime (UTC):
• Milliseconds to DateTime (UTC):
• DateTime (UTC) to seconds:

3) JavaScript (Node.js and Browsers)

• Seconds to Date:
  • new Date(1700000000 * 1000)
• Milliseconds to Date:
  • new Date(1700000000000)
• Date to seconds (UTC):
  • Math.floor(new Date('2023-11-14T00:00:00Z').getTime() / 1000)
• Format as ISO 8601 UTC:
  • new Date().toISOString() // e.g., 2023-11-14T12:34:56.789Z

Tip: Always multiply seconds by 1000 when constructing Date from epoch seconds.

4) Python (datetime)

• Seconds to UTC datetime:
  • from datetime import datetime, timezone
  • dt = datetime.fromtimestamp(1700000000, tz=timezone.utc)
• Milliseconds to UTC:
  • dt = datetime.fromtimestamp(1700000000000 / 1000, tz=timezone.utc)
• Datetime to epoch seconds:
  • int(datetime(2023, 11, 14, tzinfo=timezone.utc).timestamp())

5) Java (java.time)

• Seconds to Instant:
  • Instant.ofEpochSecond(1700000000)
• Milliseconds to Instant:
  • Instant.ofEpochMilli(1700000000000L)
• Instant to ZonedDateTime (UTC):
  • Instant.ofEpochSecond(1700000000).atZone(ZoneOffset.UTC)
• ISO 8601 formatting:
  • DateTimeFormatter.ISO_INSTANT.format(Instant.now())

6) PHP (DateTime)

• Seconds to DateTime (UTC):
  • (new DateTime('@1700000000'))->setTimezone(new DateTimeZone('UTC'))
• Milliseconds:
  • $dt = DateTimeImmutable::createFromFormat('U.u', '1700000000.000');
  • $dt = $dt->setTimezone(new DateTimeZone('UTC'));
• DateTime to epoch seconds:
  • $dt->getTimestamp()

7) Go (time)

• Seconds to time.Time UTC:
  • time.Unix(1700000000, 0).UTC()
• Milliseconds to time.Time:
  • time.UnixMilli(1700000000000).UTC()
• time.Time to epoch seconds:
  • t.UTC().Unix()

8) SQL Dialects

• PostgreSQL:
  • To timestamp (UTC assumed): to_timestamp(1700000000) AT TIME ZONE 'UTC'
  • To epoch seconds: EXTRACT(EPOCH FROM TIMESTAMP '2023-11-14 00:00:00+00')
• MySQL 8+:
  • FROM_UNIXTIME(1700000000) -- returns DATETIME in session time zone
  • UNIX_TIMESTAMP('2023-11-14 00:00:00')
  • Use CONVERT_TZ for explicit zones.
• SQLite:
  • datetime(1700000000, 'unixepoch') -- UTC
  • datetime(1700000000, 'unixepoch', 'localtime')
• BigQuery:
  • TIMESTAMP_SECONDS(1700000000)
  • UNIX_SECONDS(TIMESTAMP '2023-11-14 00:00:00+00')

Note: Control time zones explicitly in SQL to avoid session defaults.

9) Excel

• Excel’s epoch starts 1899-12-30 (Windows). To convert epoch seconds (in A2) to local date:
  • =A2/86400 + DATE(1970,1,1)
  • Then format cell as Date/Time.
• For milliseconds (in A2):
  • =A2/86400000 + DATE(1970,1,1)

Note: Excel will display in your local time and locale.

10) Google Sheets

• Seconds (A2):
  • =A2/86400 + DATE(1970,1,1)
• Milliseconds (A2):
  • =A2/86400000 + DATE(1970,1,1)
• Force UTC display via custom formatting or perform offset math when needed.

11) Shell/Bash Quick Checks

• Current epoch seconds:
  • date +%s
• Convert now to ISO 8601 UTC:
  • date -u +"%Y-%m-%dT%H:%M:%SZ"

Real World Examples

• Log analysis: Your app logs an error at epoch 1700000000. Convert to UTC, then to the service’s time zone for incident timelines.
• API payloads: A webhook posts timestamps in milliseconds. Normalize to seconds on ingest and store as INT to save space.
• IoT data: Sensors send epoch ms. Batch jobs convert to ISO 8601 for downstream BI tools.
• Scheduling: Store job times as epoch seconds in UTC. Convert to users’ local time on display and confirmation emails.
• Analytics windows: Use numeric range filters (e.g., last 24 hours) without parsing strings.

Common Mistakes

• Mixing units: Treating 1700000000 as ms instead of s (or vice versa). Check length: 10 vs 13 digits.
• Ignoring time zones: Displaying UTC without a clear label or converting incorrectly to local time.
• DST surprises: Adding 24 hours across a DST boundary may not land at the same local clock time.
• 2038 problem: 32‑bit signed time_t overflows on 2038‑01‑19. Use 64‑bit representations.
• Leap seconds: Epoch time ignores them. Don’t expect alignment with official leap‑second inserts.
• Manual math: Rebuilding calendars yourself invites bugs. Use official time libraries.

Best Practices

• Store canonical timestamps as epoch seconds (64‑bit) in UTC.
• Exchange timestamps in ISO 8601 (e.g., 2023-11-14T00:00:00Z) when reading/writing APIs.
• Convert to local time only for presentation; keep UTC internally.
• Always label time zones and units in logs, payloads, and docs.
• Validate input lengths and ranges (10 vs 13 digits; reasonable date bounds).
• Centralize time utilities to avoid fragmentation across services.
• Use scheduled tasks with time zone–aware libraries to avoid DST issues.

Expert Tips

• Prefer monotonic clocks (e.g., time.monotonic in Python) for durations, not epoch time.
• When pagination uses timestamps, include tiebreakers (id) to avoid duplicates on equal times.
• In analytics, precompute day/week buckets using UTC boundaries to ensure consistency.
• Cache time zone conversions when rendering large tables to improve performance.
• In mobile apps, store server time alongside device time to detect skew.

Comparison Table

Frequently Asked Questions

1. What is Unix epoch time?

Unix epoch time is the number of seconds (or milliseconds) since 1970-01-01 00:00:00 UTC, excluding leap seconds.

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

Count digits. Seconds are usually 10 digits; milliseconds are 13. Also check documentation for the source system.

3. Does epoch time include time zones?

No. Epoch is measured in UTC. Convert to a time zone only for display or user-specific logic.

4. How do I convert epoch to local time?

Convert epoch to a UTC date/time object, then apply the target time zone. Use built-in functions for accuracy.

5. Why is my converted time off by several hours?

Likely a time zone mismatch or DST issue. Confirm whether you’re showing UTC or local time and which offset applies.

6. What about leap seconds?

Unix time ignores leap seconds. Time appears continuous; official leap-second adjustments aren’t represented.

7. Is the Year 2038 problem still relevant?

Only for 32‑bit time_t systems. Use 64‑bit types or modern libraries to avoid overflow.

8. Should I store seconds or milliseconds?

Store seconds unless you truly need sub-second precision. Milliseconds increase storage costs and complexity.

9. How do I convert an ISO 8601 string to epoch?

Parse the string with a time library that understands time zones, then call the function that returns epoch seconds.

10. Are negative epoch values valid?

Yes, they represent times before 1970-01-01 UTC. Ensure your database and language support them.

11. How do I convert epoch in Excel?

For seconds in A2: =A2/86400 + DATE(1970,1,1). Then format as Date/Time.

12. Which SQL function should I use?

It depends on your database: to_timestamp/EXTRACT in PostgreSQL, FROM_UNIXTIME/UNIX_TIMESTAMP in MySQL, datetime/strftime in SQLite, TIMESTAMP_SECONDS/UNIX_SECONDS in BigQuery.

13. Can I safely do arithmetic with epoch times?

Yes for durations and offsets, but convert to zoned time when aligning with calendar dates or DST-sensitive events.

14. How do I avoid DST issues in scheduling?

Store and compute in UTC. Convert to the user’s time zone at display time. Use libraries that handle DST transitions.

15. Is ISO 8601 better than epoch?

They serve different purposes. Epoch is compact for storage/comparison; ISO 8601 is human-readable and explicit about time zones. Use both appropriately.

External References

• MDN Web Docs: Date and time in JavaScript – https://developer.mozilla.org/docs/Web/JavaScript/Reference/Global_Objects/Date
• Python datetime – https://docs.python.org/3/library/datetime.html
• Java java.time – https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html
• GNU date – https://www.gnu.org/software/coreutils/manual/html_node/date-invocation.html
• PostgreSQL date/time functions – https://www.postgresql.org/docs/current/functions-datetime.html
• MySQL date/time functions – https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
• SQLite date/time functions – https://www.sqlite.org/lang_datefunc.html
• BigQuery date/time functions – https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
• IANA Time Zone Database – https://www.iana.org/time-zones
• Google Search Central: Helpful content – https://developers.google.com/search/docs/fundamentals/creating-helpful-content
• W3C Date and Time Formats (ISO 8601) – https://www.w3.org/TR/NOTE-datetime
• Schema.org Date/Time – https://schema.org/DateTime

Conclusion

Unix time is a compact, universal way to represent moments in UTC. With the right functions, you can convert values quickly and safely in any environment. By standardizing on epoch seconds for storage and ISO 8601 for exchange, you’ll reduce bugs and improve clarity across teams and systems.

Call To Action

Ready to work faster? Use ZenixTools to convert unix epoch time instantly, validate units, format ISO 8601 strings, and compare results across time zones. Explore our time utilities to streamline development, debugging, and analytics today.

Internal Link Suggestions (ZenixTools)

• ZenixTools Unix Timestamp Converter
• ZenixTools ISO 8601 Date Formatter
• ZenixTools Time Zone Converter & Lookup
• ZenixTools Date Difference Calculator
• Blog: Handling Time Zones and DST in APIs