How to convert date into integer number in php?

How to convert date into integer number in php?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Converting MySQL DateTime Columns to Unix Timestamps in PHP

When working with a legacy MySQL database, you might encounter columns defined as DATETIME that you want to store as integers instead. This often happens when migrating data or optimizing storage for high-frequency time-series logs where string parsing overhead is undesirable. The specific request usually involves converting a human-readable timestamp like 2012-03-27 18:47:00 into a compact integer representation, such as the Unix epoch time (seconds since January 1, 1970).

In PHP, there are two primary ways to perform this conversion depending on whether you are transforming data at the database level or handling it within your application logic.

Using MySQL Functions Directly

If your goal is to physically change the column datatype from DATETIME to an integer type (like BIGINT), the most efficient approach is to use a SQL migration script that leverages MySQL's built-in functions. The function UNIX_TIMESTAMP() returns the Unix timestamp as an integer.

You can execute a direct conversion using the following SQL statement:

ALTER TABLE your_table_name 
MODIFY COLUMN datetime_column BIGINT UNSIGNED,
ADD COLUMN new_datetime_column DATETIME;

UPDATE your_table_name 
SET new_datetime_column = FROM_UNIXTIME(UNIX_TIMESTAMP(datetime_column)),
    datetime_column = UNIX_TIMESTAMP(datetime_column);

-- Drop the original column once conversion is verified
ALTER TABLE your_table_name DROP COLUMN datetime_column;

This method ensures that the data is cast to a standard integer format immediately. However, be aware of potential pitfalls. MySQL DATETIME values are limited to the range between '1000-01-01' and '9999-12-31'. If your column contains dates outside this range, or if you are using a specific timezone context that isn't handled by the default session, UNIX_TIMESTAMP() might return unexpected values. Additionally, ensure you have sufficient disk space for the temporary tables created during the ALTER TABLE operation, especially on large datasets.

Converting in PHP Application Logic

If you prefer to keep the data as a string or handle the conversion within your application layer before storing it, PHP provides robust tools for this task. The DateTime class combined with strtotime() is the standard idiom.

$dateString = '2012-03-27 18:47:00';
$timestamp = strtotime($dateString);

// $timestamp will be an integer like 1332906420
echo $timestamp;

While the source material mentioned a specific example output of 131221154, it is important to note that standard Unix timestamps for March 2012 are in the billions (e.g., 1332906420). A number like 131221154 would correspond to a date around October 1973, which suggests a potential misunderstanding of magnitude in the original query or a specific truncation logic not standard to PHP. Always verify the expected integer range against your system's architecture. If you are dealing with microseconds, consider using microtime(true) or parsing the fractional seconds manually, though this requires careful handling to avoid floating-point precision issues when storing integers.

Handling Timezones and Storage Constraints

A critical trade-off in converting dates to integers is the loss of timezone information inherent in the string format. An integer timestamp represents a specific moment in absolute time (UTC). If your application expects local times, you must explicitly convert the integer back to a localized DateTime object before displaying it to the user.

$intTime = 1332906420;
$dateObj = new DateTime('@' . $intTime); // The '@' prefix tells DateTime to interpret as timestamp
echo $dateObj->format('Y-m-d H:i:s');    // Output: 2012-03-27 18:47:00 (UTC)

Furthermore, if you are using a framework like Laravel, the Carbon package simplifies this by providing fluent interfaces that handle timezone conversions automatically. While not strictly necessary for simple integer conversion, it adds a layer of safety against common timezone bugs.

Final Considerations on Data Integrity

Before committing to changing a column type from DATETIME to an integer, ensure your application codebase is updated to handle the new format. Any existing queries that assume a string date (e.g., using LIKE '2012%') will break immediately after conversion. Always backup your data and run the migration in a staging environment first. The integer approach offers faster indexing and sorting but removes the flexibility of partial date matching inherent to string columns.

Our network