Database Timestamp Converter

Convert between Unix timestamps and human-readable dates, or translate between MySQL, PostgreSQL, and Oracle timestamp formats. Enter a Unix timestamp (seconds, milliseconds, or microseconds) to get the formatted date-time string, or provide a date and time to get the corresponding timestamp. Choose your preferred database format (MySQL DATETIME, PostgreSQL TIMESTAMPTZ, Oracle TO_DATE, ISO 8601) and see the converted output plus a ready-to-use SQL snippet.

Enter seconds (10 digits), milliseconds (13 digits), or microseconds (16 digits)

Used when converting Date → Timestamp

hrs

Set to 0 for UTC. Use negative values for west of UTC (e.g. -5 for EST).

Results

Formatted Date & Time

--

Unix Timestamp (seconds)

--

Unix Timestamp (milliseconds)

--

SQL Snippet

--

UTC String

--

Days Since Unix Epoch

--

Frequently Asked Questions

What is a Unix timestamp?

A Unix timestamp (also called Unix time, POSIX time, or epoch time) is the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC, not counting leap seconds. It is a widely used system for tracking time in databases, programming languages, and web applications because it is timezone-independent and easy to compare or calculate with.

How do I store a Unix timestamp in MySQL?

MySQL supports several timestamp-related types. Use DATETIME to store a human-readable date like '2023-11-14 22:13:20', or use INT (unsigned) to store the raw Unix timestamp integer. The FROM_UNIXTIME() and UNIX_TIMESTAMP() functions convert between the two formats within SQL queries.

What is the difference between MySQL DATETIME and PostgreSQL TIMESTAMPTZ?

MySQL DATETIME stores date and time without any timezone information, so the application must manage timezone context separately. PostgreSQL TIMESTAMPTZ (timestamp with time zone) stores the timestamp in UTC internally and automatically converts to the session's timezone when retrieving data, making it the safer choice for multi-timezone applications.

How does Oracle store timestamps?

Oracle uses the TO_DATE() and TO_TIMESTAMP() functions to convert string literals into date/time values. The TIMESTAMP data type stores fractional seconds as well. For timezone awareness, Oracle provides TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE types. The equivalent of Unix epoch conversion in Oracle is: TO_DATE('1970-01-01','YYYY-MM-DD') + (unix_seconds / 86400).

What is the Year 2038 Problem?

The Year 2038 Problem occurs because many older systems store Unix timestamps as a signed 32-bit integer, which can hold a maximum value of 2,147,483,647 — corresponding to January 19, 2038, 03:14:07 UTC. After that point, a 32-bit signed integer overflows to a negative number, causing date errors. Modern systems use 64-bit integers, which can represent timestamps billions of years into the future.

Does this converter handle milliseconds and microseconds?

Yes. The converter automatically detects whether your input is in seconds (10 digits), milliseconds (13 digits), or microseconds (16 digits) and normalizes it to seconds before performing the conversion. JavaScript's Date object uses milliseconds internally, so the conversion is handled precisely.

How do I convert a date back to a Unix timestamp in SQL?

In MySQL, use UNIX_TIMESTAMP('2023-11-14 22:13:20'). In PostgreSQL, use EXTRACT(EPOCH FROM '2023-11-14 22:13:20'::timestamp). In Oracle, use (TO_DATE('2023-11-14 22:13:20','YYYY-MM-DD HH24:MI:SS') - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400. This tool generates the appropriate SQL snippet based on your chosen database format.

What is ISO 8601 format and when should I use it?

ISO 8601 is an international standard for representing dates and times, formatted as YYYY-MM-DDTHH:MM:SSZ (e.g. 2023-11-14T22:13:20Z). It is unambiguous, sortable as a string, and widely supported across programming languages and APIs. It is the recommended format for data interchange, REST APIs, and log files regardless of which database backend you use.

More Time & Date Tools