UNIX Timestamp vs. MySQL Date Format

[sid]

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 ;-)

[rgermain]

I found that I prefer the Unix Timestamp also. To me just seems easier to use. That might just be me though! :)

[Moonglobe]

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

[Weedpacket]

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.

[Merve]

I voted for 1.21 gigawatts because i would use whatever one is best for the situation.

[ahundiak]

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.

[drawmack]

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.

[jebster]

I usually use MySQL date fomat. So where's the code I'm suppose to critique? :p

[Moonglobe]

i suppose this is more of an "idea critique"

[jebster]

Guess I misunderstood BuzzLY's post
From Rule Breakers please heed...
This forum is for complete, working code only.

:D

[Moonglobe]

language (spoken) is a code in a sense.... and it certainly works, most of the time :p

[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 calendar implementation seems to fall down. I'm checking this.)

[jebster]

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 calendar 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! :D

**EDIT**
Oops, apparently I can't even remember how to spell! :o

[Weedpacket]

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! :D 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.

[jebster]

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 :D

[pauluskc]

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.

[dream.scape]

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)

[dream.scape]

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

[Megahertza]

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.

[t0maz]

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?

[bradgrafelman]

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?).

[Houdini]

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'?

[Weedpacket]

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 (http://www.catb.org/~esr/jargon/html/N/nybble.html) and related terms.

[yavou]

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

[peterdays]

i use UNIX tmestamp, dont even know how to use mysql's lol

[pauluskc]

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.

[Weedpacket]

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).I looked into the source code to see how PostgreSQL handles this, and found (unsurprisingly) that it implements all such descriptive statistic calculations (average, standard deviation and so on) using the accumulating algorithm to avoid overflow; if you have the average of ten numbers that's enough to work out the new average when you add an eleventh....

But I'm at a bit of a loss as to why you'd want to average a list of dates.

[pauluskc]

Trying to understand what you are saying.... :) too distracted by your moniker.....ok got it. slower on a friday afternoon...

I think you mistook "outside the normal range" ... I meant that the average date could end up being something like 2009-37-92 67:28:83, not that the number would be outside the range of the BIGINT.

The funky would be a particularly hard to handle flaw in that method of calculating the average date. I'm not sure how wraparond rules would apply, like the month of 2009-37 should that really be interpreted as 2011-11?

How do astronomers store the ages of stars... perhaps just # of years vs. year-month-day, eh? :eek:

From your description of the way PG does it's stats, it's like it's keeping a running tally of the average to avoid overflowing, which is a great idea, but potentially adds a little slowness since it's calculating the average continuously, especially over 1M records... Good to know, something more to ponder late at night.

[pauluskc]

Why to average a list of dates?

:cool: Average date of birth of your member base perhaps? a bit of a stretch there.

:cool: A query into common birth-dates to determine when to run Trojan condom commercials/text-ads.

:cool: could come up with some more.....going to need time though.

[Weedpacket]

A query into common birth-dates to determine when to run Trojan condom commercials/text-ads.All it does here is avoid the conversion into ages first. And then the user base turns out to be evenly split between people in their early teens and those in their mid-thirties? Demographics Fail.

potentially adds a little slowness since it's calculating the average continuously...while performing the querying and filtering, instead of having to store it all (somewhere) and fetch it all back again afterwards.