To convert a Unix timestamp to a date in SQL, first confirm whether the stored number is epoch seconds or milliseconds, then pass the correct unit to your database's epoch-to-date function and choose the display time zone deliberately. A Unix timestamp counts time from 1970-01-01T00:00:00Z. Unix time itself is defined in seconds; a millisecond value is 1,000 times larger. That distinction matters because decoding a 13-digit millisecond value as seconds produces an implausible year rather than the intended moment. SQL databases expose different functions for the same operation, so the safest workflow is to identify the source and unit, decode one value with the Unix Timestamp Converter, and use that human-readable result as a checkpoint while writing or testing the query. The converter runs entirely in your browser and shows ISO 8601, UTC, device-local time, and a relative label at once. It also converts a date back to epoch seconds and milliseconds. This makes it useful before a query, during debugging, or when checking a result returned by FROM_UNIXTIME(), to_timestamp(), DATEADD(), or another database expression. In particular, a UTC output gives you a stable reference for comparing against the SQL result, while local time helps you recognize whether a session time-zone setting has changed the presentation.
What SQL Is Actually Converting
A Unix timestamp is an integer count from the Unix epoch rather than a formatted calendar field. It carries no separate time zone, so the number identifies one instant while the output layer decides how that instant appears. The Unix time reference defines the epoch convention used by systems such as logs, APIs, databases, and scheduled jobs. SQL engines then apply their own return types and time-zone rules, which is why a timezone-naive datetime, a timezone-aware value, and a formatted string may not be directly comparable.
Before any conversion function runs, establish the source unit. Unix time uses seconds as its base unit, while milliseconds are 1,000 times that value. A current epoch-seconds number usually has 10 digits, and epoch milliseconds usually have 13. Digit length is a useful clue rather than proof: an older record, synthetic fixture, or nonstandard API can look different. The schema, data producer, or field name remains the stronger source of evidence.
| Stored representation | Scale | SQL treatment |
|---|---|---|
| Epoch seconds | Base Unix unit | Pass directly to a seconds-based conversion function. |
| Epoch milliseconds | 1,000 × epoch seconds | Divide by 1,000 or use an expression that explicitly adds milliseconds. |
Do not assume a value uses seconds solely because it came from a Unix-named field. Confirm the scale once in the schema or producer documentation, then keep that unit documented beside queries and dashboards. This check prevents the most damaging error: a valid number interpreted at the wrong scale.
Decode a Timestamp for a SQL Query
Use the converter as a pre-query checkpoint. It turns one raw value into four readable views and performs the calculation locally, giving you an expected result before you write dialect-specific SQL.
- Copy the raw value exactly. Preserve every digit and check whether it came from a database column, API payload, log line, or exported file.
- Open the Timestamp → Date field. Paste the Unix value into the converter without adding a prefix or converting it to another format first.
- Select the correct unit. Choose Seconds or Milliseconds. The converter auto-detects by digit count, but override that selection when the schema or producer gives you a definitive answer.
- Read all four results. ISO 8601 and UTC are timezone-independent. Local time reflects your device's timezone, while Relative shows the distance from the current moment.
- Run the reverse check when useful. Enter a date such as 2023-11-14T22:13:20Z in the Date → Timestamp field to obtain the corresponding epoch value in both seconds and milliseconds.
Use the ISO 8601 or UTC result as the SQL comparison target because it does not depend on your device's timezone. Use Local time when investigating an event in your own timezone, and use Relative when you need to judge its age quickly. The Date → Timestamp panel also accepts a plain UTC date or a natural date, making it useful for checking values that arrive in several formats. A round-trip test is straightforward: obtain the timestamp for a known date, decode it, convert the date back, and confirm that the same epoch value returns.
Run the Native Conversion in Your Database
Once the expected UTC value is known, select the expression supported by your database. In the following examples, epoch_seconds represents a column or bound value containing epoch seconds.
- Match the function to the database engine. MySQL, PostgreSQL, SQL Server, and SQLite do not share one portable epoch-to-date function.
- Substitute the source column or parameter. Keep the unit visible in the column name, query parameter, or nearby documentation.
- Decide how the result should be displayed. UTC is the most stable value for comparisons, while a session or client time zone may produce a different local representation.
- Compare the database result with the converter's UTC output. If they disagree, check the unit before changing the time-zone setting.
| Database | Expression for epoch seconds | Result to verify |
|---|---|---|
| MySQL | FROM_UNIXTIME(epoch_seconds) | Returns a date/time value, with the session time zone affecting its presentation. |
| PostgreSQL | to_timestamp(epoch_seconds) | Returns a timestamp with time zone; the session time zone controls its display. |
| SQL Server | DATEADD(second, epoch_seconds, CAST('1970-01-01T00:00:00+00:00' AS datetimeoffset(7))) | Adds the second offset to a UTC-aware origin. Check the value against DATEADD's accepted offset range. |
| SQLite | datetime(epoch_seconds, 'unixepoch') | Produces UTC date/time text because of the 'unixepoch' modifier. |
The SQL Server expression starts with an explicit UTC offset, while SQLite's 'unixepoch' modifier requests UTC output directly. MySQL and PostgreSQL can present the same instant in the session time zone, so a correct instant may still look different in the client. For a wider function-by-function reference across database engines, see How to Convert a Unix Timestamp in SQL Queries.
Convert Millisecond Values to SQL Dates
A millisecond column must not be passed directly to a function that expects seconds. Normalize it according to the precision required by your application:
- MySQL: FROM_UNIXTIME(epoch_ms / 1000)
- PostgreSQL: to_timestamp(epoch_ms::double precision / 1000.0)
- SQL Server: DATEADD(millisecond, epoch_ms % 1000, DATEADD(second, epoch_ms / 1000, CAST('1970-01-01T00:00:00+00:00' AS datetimeoffset(7)))). This preserves the millisecond remainder, subject to the function's offset range.
- SQLite: strftime('%Y-%m-%dT%H:%M:%fZ', epoch_ms / 1000.0, 'unixepoch')
Using 1000.0 in PostgreSQL and SQLite avoids an integer-only division. The SQL Server form separates whole seconds from the remaining milliseconds. A conversion that discards the remainder may disagree with a high-precision ISO 8601 value even when the displayed second is correct.
Match Time Zones Before Declaring a Failure
Start every comparison with UTC. The converter's UTC and ISO 8601 views do not change with the device timezone, making them stable expected values for a test. The Local time view follows the timezone configured on the reader's device, so it is useful for personal context but not a universal test fixture.
Compare the same instant rather than the spelling of the date. If the calendar date and clock time differ, test the seconds-versus-milliseconds selection first. If the instant is correct but its display differs, inspect the database session or client timezone. If the values are close rather than identical, check rounding, truncation, and whether the source preserves fractional seconds. The Relative result is a quick age check, not a field to compare directly with a SQL expression.
Troubleshoot Unit, Time Zone, Precision, and Range
Most conversion surprises come from the unit scale, time-zone presentation, precision, or storage range. The converter makes the first three visible; the fourth depends on the database schema and the function being used.
Wrong Unit
A millisecond value treated as seconds is scaled incorrectly and can produce a far-future date. This is particularly easy to miss when the query still returns a valid-looking timestamp. Confirm the producer's unit, check the converter's selected Seconds or Milliseconds control, and normalize the value before running the query.
Time-Zone Presentation
A Unix timestamp identifies the same instant everywhere, but SQL functions can return a value formatted for the current session or client timezone. Compare the instant in UTC before changing the query. If UTC agrees with the converter, the underlying conversion is sound and only the presentation needs adjustment.
Subsecond Precision
Millisecond values may be rounded or truncated by a database function, a client driver, or a display format. Compare the ISO 8601 fractional portion with the raw column and the SQL result. If only the fraction differs, preserve the source precision or choose a format that exposes it.
The Year 2038 Boundary
Systems that store Unix time in a signed 32-bit integer overflow at 03:14:07 UTC on 19 January 2038 and wrap back to 1901. A 64-bit storage type is not affected by that integer limit, and modern browsers use 64-bit doubles, so the converter can handle dates far beyond 2038. Legacy embedded systems and database code can still contain the bug. This is a range problem rather than a seconds-versus-milliseconds problem, although an individual SQL function may impose its own argument limit.
Keep the Verification Workflow Repeatable
A dependable process is short: identify where the number comes from, determine its unit, decode it in the Unix Timestamp Converter, run the matching database expression, and compare against the converter's UTC result. If the results differ, change one variable at a time—unit, then time zone, then precision—rather than editing the query blindly.
Every conversion in the tool is computed in your browser with the platform Date object and explicit UTC formatting. The pasted value is not uploaded, so the same workflow can be used for production log entries, API payloads, and local development data. After the SQL result agrees with the converter, document the raw column's unit in the schema. A clear name such as epoch_ms, or a standard migration to epoch seconds, removes the ambiguity for the next developer.