RE: Storing mysql dates as an integer [SOLVED]
Fred, An unsigned int is probably more appropriate for timestamps than a signed int. Thank you, that was the kind of advice I was looking for. And your point about not using UNIX time stamps for birthdays and things that could well be outside their date range is well taken. I'm only using it for events and people signing up for those events, so all the dates should be clustered around the present time. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing mysql dates as an integer
An unsigned int is probably more appropriate for timestamps than a signed int. Also note that the range for timestamps is 1970 until 2038 (or 2106? for unsigned ints), where DATE and DATETIME have a much greater range. Using a UNIX timestamp for birthdays might not be appropriate. Many Unix systems are transitioning to a 64-bit timestamp: that should cover most needs...(:-). In the meantime, while there may be a some applications where it is important to keep both the date AND time for a range outside of 1970-2038, I can't think of one outside of astronomy. Most applications that need dates outside of that range don't need much in the way of times in general and timezone adjustments in particular. When I am storing event timestamps, I use Unix 32-bit timestamps (which are in GMT) as the base and either convert to local time for printing or store a printed version (in -mm-ddThh:mm:ss- form) in anotehr column where appropriate. Storing the latter makes date-based selection easy. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing mysql dates as an integer
At 9:57 +0900 1/8/04, Dave G wrote: MySQL Gurus, Because of time zone differences between my web site's intended audience (Japan) and my server's location (Nevada), I've found that it's most efficient, or at least most simple, to do all my time calculations in PHP. Up to now, I've still been storing all my dates in native MySQL time formats - like DATETIME. However, this requires a bit of format conversion between PHP and MySQL. What I'm thinking now is that it might just be easier for me to store the date as a UNIX timestamp format in MySQL as a simple ten digit long integer (Unix timestamps are ten digits, aren't they?). That way I No, they're fourteen digits: CCYYMMDDhhmmss can pass them to and from PHP and do all the work on the PHP side without having to do any format conversions in my SELECT and INSERT queries. Is this a horribly bad idea for any reason? Is the INTEGER format the best suited for this purpose? Any tips would be greatly appreciated. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing mysql dates as an integer
Paul, (Unix timestamps are ten digits, aren't they?). No, they're fourteen digits: CCYYMMDDhhmmss I think we might be talking about different things. A UNIX time stamp is ten digits long: http://www.unixtimestamp.com/ How MySQL stores it's own TIMESTAMP column is the format you describe. I want to know if there's any problem storing a UNIX timestamp purely as an integer in my MySQL database. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing mysql dates as an integer
At 11:25 +0900 1/8/04, Dave G wrote: Paul, (Unix timestamps are ten digits, aren't they?). No, they're fourteen digits: CCYYMMDDhhmmss I think we might be talking about different things. A UNIX time stamp is ten digits long: http://www.unixtimestamp.com/ How MySQL stores it's own TIMESTAMP column is the format you describe. I want to know if there's any problem storing a UNIX timestamp purely as an integer in my MySQL database. I think not, as long as you don't treat it like a TIMESTAMP column. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
