I've been using the unix timestamp all my life.
I like it because it's easy to compare, it's fast because I store it as an integer. And since I'm using PHP, I can get any date/time format with date() function from the unixtimestamp.
Now, some people are saying that it's best to use the DATETIME format. But besides the more suited name, I don't see any advantages.
Is it indeed better to use DATETIME, if so, what are the advantages?
Equivalent of WeekDay Function of VB6 in C#
Shell script to compare dates from multiple files in Linux
How to format a Date without using code - Format String Question
You have to convert them to the formats you want to use, you have to do the calculations between date ranges, you have to build the queries to get data in a range.
Should I use field 'datetime' or 'timestamp'?
This seems counter-intuitive- surely your "programmer time" is best spent solving real problems?.
Natural/Relative days in Python
It seems much better practice to store dates and times in the proper format that MySQL has available, then use the database functions to create the queries for the data you want.
How can I parse dates and convert time zones in Perl?
The time you would waste doing all the convertions and mucking about is massive compared to the afternoon spent reading (and understanding) 11.6 MySQL Date and Time Functions.
Limiting a date range with exactness in MS SQL / SQL Server 2005
ASP.NET AJAX and DateTime Format on the Client
DATEtype also has the advantage in that its only storing day, month and year so there is no space wasted or comparison complication that a seconds since epoch time would have for situations where you only cared about the day and not the time.. Personally I tend to use a database as just a dump for data so such functions are of little interest.
In PHP I tend to just store the date in integer format for pretty much the reasons you state..
So instead of.
, which converts every row on the table to compare it to the date you've got.
select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2
You should use.
Doing it this way WILL use the appropriate table indexes.. @treznik a while ago I moved from a uts integer to a datetime or timestamp data types, for the reasons mentioned above, in that they're much easier to read and manipulate (I do quite a lot of direct table access).
select col1,col2,colUnixdatetime from table where colUnixdatetime between UNIX_TIMESTAMP(wtvdate1) and UNIX_TIMESTAMP(wtvdate2).
However I've lately started to re-think this approach for two reasons:.
- There is no time zone location stored, so you're inferring the time zone based on your location.
This may or may not be an issue for you.
- It ignores daylight saving time.
So when the clocks go back at 2am, you will get 1:30am twice, and saying 2011-10-30 01:30 doesn't let you know this, whereas 1319938200 does.
I don't think there's a native way in mysql to store date including time zone, except as a string (2011-10-30 01:30 BST).
But I think the correct answer is: "depends".
I recently did a single table database where I wanted to only list URLs.
There would be a date field, but the date field is purely for sorting.
I.e order by last_crawled.
Which means I will never use any built-in date functions on that field.
It is merely an easy way to get the oldest entries first and I will never apply date functions to this field.
Now, had I made this a date field, I would have lost out on two things:.
- A datetime field is twice the size of an integer
- Sorting by an integer is faster (not 100% sure of this, pending outcome of this question)
This made using internal mysql date functions possible which turned out to be very useful when we had to start doing reports..
Seeing the actual date when you do:.
is pretty nice..
select * from table where ...
Using this function in where clause will completely ignore any index usage.. say my query is:. select col1,col2,colUnixdatetime from table where colUnixdatetime between wtvdate1 and wtvdate2. I would need to run:. select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2. This above query will completely ignore any indexes, well there is no use of indexes here as they will never be used coz I will always have to use a function to get the real date time.. Any in-built function used on LHS of condition in a where clause would not use any indexes and if you have a HUGE table, your query will take longer..