how do you prefer to store dates in a database?
i personally ONLY use unix timestamps - it just proved to be the most flexible way to operate with dates. yep - true - i compromise by losing the mysql date functions, but oh well ;-)
UNIX Timestamp vs. MySQL Date Format
I found that I prefer the Unix Timestamp also. To me just seems easier to use. That might just be me though! 
i just store dates as type date and then do SELECT UNIX_TIMESTAMP(col).... lets me use all the date functions and still get a timestamp
MySQL timestamps give greater dynamic range (if you need, e.g., dates in the 16th or 25th Centuries); but you're asking for hassles if you don't use MySQL's own date/time functions to work with them before returning query results, and they are inaccurate pre Gregorian calendar. Unix timestamps fail for dates after January 2038, but that's a platform-dependent limit.
21 days later
I voted for 1.21 gigawatts because i would use whatever one is best for the situation.
You left out using a char field and storing time stamps as YYYYMMDDHHMMSS
Platform independent, database independent, human readable, nice big date range though I admit it you go back a few centuries then things can more complicated.
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.
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?