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.

        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.

                          pauluskc wrote:

                          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.

                            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.

                              Why to average a list of dates?

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

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

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

                                pauluskc wrote:

                                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.

                                  Write a Reply...