depends on my mood that day, since I have classes built for working with both it doens't make a whole lot of difference from a programming standpoint.
UNIX Timestamp vs. MySQL Date Format
I usually use MySQL date fomat. So where's the code I'm suppose to critique? :p
i suppose this is more of an "idea critique"
Guess I misunderstood BuzzLY's post
From Rule Breakers please heed...
This forum is for complete, working code only.
language (spoken) is a code in a sense.... and it certainly works, most of the time :p
Oh well, if we're going to critique alternate date formats, there's also the Julian Day format. Astronomers have used it for decades. Date/times are specified in days and fractions of a day since midday, 1st January 4713BC. (Julian calendar; i.e., no Gregorian correction - which is where PHP's [man]calendar[/man] implementation seems to fall down. I'm checking this.)
Originally posted by Weedpacket
Oh well, if we're going to critique alternate date formats, there's also the Julian Day format. Astronomers have used it for decades. Date/times are specified in days and fractions of a day since midday, 1st January 4713BC. (Julian calendar; i.e., no Gregorian correction - which is where PHP's [man]calendar[/man] implementation seems to fall down. I'm checking this.)
Do you just know all these things or do you look up the details when posting it? Cause you must have one wicked bad memory! I can't even remember to eat breakfast in the morning or go to sleep at night!
EDIT
Oops, apparently I can't even remember how to spell! :o
Originally posted by jebster
Do you just know all these things or do you look up the details when posting it? Cause you must have one wicked bad memory! I can't even remeber to eat breakfast in the morning or go to sleep at night!
I do it by not bothering to remember useless information, like when to do the laundry, check the tyre pressure on my car, or what I'm supposed to be buying for dinner.
Originally posted by Weedpacket
I do it by not bothering to remember useless information, like when to do the laundry, check the tyre pressure on my car, or what I'm supposed to be buying for dinner.
Buy stuff for dinner? I just stock up on cans of ravioli, tomato soup, chicken noodle soup, and Kirkland Ice tea, then I'm good for months
2 years later
I know, I know, long dead conversation (going on 2 years) but I was perusing and thought that a couple bits would benefit. AnyWho...
Part of the other reason for choosing the format to store dates is the speed of the system and the number of entries. Imagine 1,000,000 records.
The MySQL Timestamp field (YYYYMMDDHHMMSS) = 14 characters (bytes) per record. 14,000,000 bytes to store, 14,000,000 bytes to load in an index, etc.
The UNIX_TIMESTAMP (PHP: time()) field is an integer, requiring whatever your system uses for integers (usually 32-bits, aka 4 bytes). 4,000,000 bytes in the index or hard drive.
Now, imagine finding the information you need. MySQL timestamp = compare 1,000,000 strings of 14 characters to find the one you are looking for. UNIX_TIMESTAMP, compares integers. greater than & less than are pretty quick and efficient comparative operators. They don't work so well with strings because they have to be converted to numbers.
So... if you need high volume with fast response and aren't building a Star Trek fan site, then you should probably use the unix_timestamp to be as scalable as possible. Imagine when that 1,000,000 grows to 10,000,000 or 1,000,000,000. Phew!!
Simple, quick, efficient. That's why I use the unix timestamp.
Although for dates outside the range, I agree, the mysql timestamp is only proper or a collection of characters representing the date.
Just my 2 bits to the argument.
I use the ISO 8601 format YYYY-MM-DD HH:MM:SS because:
1. it is human readable
2. it is an international standard (big to small)
3. it is the SQL standard TIMESTAMP format, and thus is highly portable. (http://www.analysisandsolutions.com/code/dates.htm)
pauluskc wrote:The MySQL Timestamp field (YYYYMMDDHHMMSS) = 14 characters (bytes) per record. 14,000,000 bytes to store, 14,000,000 bytes to load in an index, etc.
Not quite. MySQL DATETIME is 8 bytes, and TIMESTAMP is only 4 bytes, not 14.
from http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html:
Storage Requirements for Date and Time Types
Data Type Storage Required
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
6 days later
I've only just started using mysql and i find it easy to use. If i need any help working with the timestamp I can just get it in this forum.
2 months later
Kinda have a question here... I always use the MySQL TIMESTAMP and I have no idea what the differences are with a Date field, but... PHP/Unix timestamp != MySQL TIMESTAMP field?
Correct. MySQL's TIMESTAMP field is a YY-MM-DD HH:MM:SS format I believe.
The Unix timestamp, however, is an integer based on the number of seconds since the Unix epoch (erm, January 1st, 1970 or so?).
Bet since you are concerned about bits, bytes, words etcetera that a bit is of course just one but two bits are a nibble 8 bits are the byte (bite) and 16 bits are a word so then what is 32 or 64 bits? I guess so far that hasn't recieved an official name maybe 'phrase' then 'sentence'?
5 days later
A "word" depends on the processor. On 32-bit processors a word is 32 bits; what it is for a 64-bit processor should be obvious 
But see nybble and related terms.
3 years later
pauluskc;10697530 wrote:Although for dates outside the range, I agree, the mysql timestamp is only proper or a collection of characters representing the date.
I just made and experiment myself. Considering mktime() won't be able to parse time before 1970. OK.. actually it did work anyway.
$myTime = mktime(0,0,0,1,1,1200); // == -24298882792
echo date("Y",$myTime); //utput is: 1200
so.. if you don't need human readable dates in your database, it's OK to use unix timestamp anyway
11 days later
i use UNIX tmestamp, dont even know how to use mysql's lol
a month later
Happy Holidays All! Not that we get holidays off work or anything nice like that.
I do stand corrected in my storage space comparison. Still need to do an query actual performance test. Quite a bit involved in the whole equation really - from programming code to database server to dozens of other things.
But, I thought I'd come back with the BIGINT for alternative date storage methods...
Nice range signed, covers from years from -922,337-20-36 85:47:75.807 to +922,337-20-36 85:47:75.807 -- using the modern calendar's day 0 as the middle of things. Nearly a million years!
Simple, standard mathematics & statistics for averages and the rest - e.g. SELECT AVG(bigint_datetime) with all dates in essentially comparable formats as 002009mmddhhmmssttt, could result in a value well outside the normal range... hmmm.. But that could be handled application or DB side too (stored procedures).
So many ways to look at it. So many ways to go - all depends on the application needs and if you want to have the application in the SQL server (stored procedures anyone?) or in the application (common function library, anyone?).
As for portability - either side of things can be very portable. PHP on most all platforms, MySQL on most platforms. Or Oracle, etc.. using standard enough SQL stuff in even the stored procedure and it's all good.
Who's got time off for the holidays? You be willing to delve into producing some comparisons of performance of MySQL's DATE functions vs. math functions? Since the datetime requires a valid date and checks it to (?), my guess is that the DATE functions are slower.
Perhaps in a couple years I'll come back and post some actual numbers.