Are MySQL datetime and timestamp fields better for PHP apps then Unix timestamp ints?

Xeoncross

2010-07-23T13:05:30

I was reading over an article that shows some really good information and benchmarks about how well the three different MySQL date/time storage options perform.

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with MyISAM

While reading the article you start to get the idea that using ints are just a waste and you should instead go with MySQL Datetime or Timestamp column types.

However, towards the end of the article he does one more test not using MySQL functions and you suddenly see that straight INT's are 2x as fast as the two MySQL options when searching by unix timestamps.

So it suddenly dawned on me - duh, what do PHP apps all use? time()! Almost every php application bases their logic off of the Unix Epoch. Which means that most queries for results in a certain time start off based on time() and then are converted to work with MySQL's fields.

This leaves me with the following:

Unix Timestamps stored as INT's are faster, take less space, and work natively with PHP's time() based calculations.
MySQL Date types are more suited to operations and logic from the MySQL side.
For the time being both Unix And MySQL Timestamps only work until 2037 which means that you must use a datetime field for larger dates in the future.
MySQL commands like date = NOW() can lag when using replication causing data inconsistencies.

So applying this to real life we see that answer that these results given that most really DBA's would use a better engine like PostgreSQL - is there arny

However, most apps that would be to the level of using DB logic would probably go with PostgreSQL. Which means that all the rest of us programmers only use MySQL for a storage tank for our data (you know it's true) which makes keeping the fields as small, fast, UNIX INT's seem like it is actually the best option.

So what do you guys think?

Are timestamps really more suited to PHP apps than the MySQL date fields?

Copyright License：
Author:「Xeoncross」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/3315508/are-mysql-datetime-and-timestamp-fields-better-for-php-apps-then-unix-timestamp

About “Are MySQL datetime and timestamp fields better for PHP apps then Unix timestamp ints?” questions

Are MySQL datetime and timestamp fields better for PHP apps then Unix timestamp ints?

I was reading over an article that shows some really good information and benchmarks about how well the three different MySQL date/time storage options perform. MySQL DATETIME vs TIMESTAMP vs INT

PHP convert MySQL datetime to Unix Timestamp

I'm confused about timezone differences when converting a MySQL datetime to Unix timestamp. It appears to me that MySQL datetimes (2011-02-07 09:45:00) are in the server's local timezone. I want to

Convert MySQL UTC datetime to UNIX timestamp

These both correctly return the current UNIX timestamp: SELECT UNIX_TIMESTAMP(LOCALTIMESTAMP()); #MySql echo time(); //PHP But I'm storing UTC_TIMESTAMPs in my database (not LOCALTIMESTAMPs). Ho...

PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

im using php 5.4.6 and MySQL 5.5.29 and I get trouble by converting a UNIX TIMESTAMP to MYSQL DATETIME and vice versa. Mysql and php wun on the same local machine. I have a simple mysql table CRE...

Convert unix timestamp to datetime with milliseconds in MySQL

Problem Converting unix timestamp to datetime while retaining milliseconds. Background I am receiving unix timestamp in the following format: 1584049707 Then I am trying to send it by means of...

Converting datetime to unix timestamp in mySQL

So here's the thing, I have the database 'example' with two tables: 'exampletable1' contains two columns 'ID','datetime' 'exampletable2' contains two columns 'id2','ti

UNIX Timestamp to MySQL DATETIME

I have a table with statistics and a field named time with Unix Timestamps. There are about 200 rows in the table, but I would like to change the Unix timestamps to MySQL DATETIME without losing the

Generate Datetime in the future without UNIX timestamp

Lets say today is the 08.20.2014. I want to get the date from "today" additional 30 years (08.20.2044) with PHP and insert it into my mysql Database with it´s Datetime field. How do I correctly

PHP MYSQL date, time, datetime and timestamp

I new to PHP and confused with date, time, datetime and timestamp. I have a MYSQL table contains date, time, datetime and timestamp. Whats the format to use from PHP to MYSQL fields? sql_time...

Which is better for storing non-Gregorian datetime in MySQL? datetime+PHP convert OR varchar OR Unix timestamp as int

I have a table which should have several columns for non-Gregorian datetime (Jalali Calendar). I need to use them in both order and where statements to show the full table content in HTML. Which sc...