View Full Version : Class->date: Store as timestamp or date string?

01-25-2003, 07:47 PM
Suppose you have a fairly simple class that serves as a data container for things that have a creation date:

class Fnord {
var $_creationDate = 0;

function getCreationDate() {
return $this->_creationDate;

function setCreationDate($date) {
$this->_creationDate = intval($date); // <- ?

Would you store the date rather as a timestamp integer (and therefore enforce an integer value in the setter method) or as a date string? Background information: This class gets it's values from a database record. The date field in the database is of type DATETIME because a) I find it more readable and b) it doesn't update automagically in MySQL like TIMESTAMP fields.

So now I ask myself if transforming this date to a timestamp while instantiating the class is actually more clever since date/time arithmetic will be quite straightforward... on the other hand, that confines me to values between 1970 and 2037. Maybe not a problem though.

As you can see, I'm currently undecided and would like to have some input from others concerning this issue.

Thanks in advance for any comments. :)

01-26-2003, 10:51 AM
It is always easier to manipulate a integer numeric than a compiled date string, so I (similar to you) always just store the timestamp and leave the formatting to the output pages.

I have tended toward just using INT(17) as the field type and insert time();, purely due to knowing the data is going to be an integer between 0 and 8^17?. As this records the exact second of the insert I can order records by minute/hour/day/week/whatever.

If I only want the day I output
echo $date("d-m-y",$row['uts']);

01-26-2003, 05:17 PM
Thanks for your input, I have now decided to stick with integer values representing a UNIX timestamp. That's IMO (after thinking a little bit over it again) the best way to store the date in a raw fashion, and since this class is not used directly in a presentation layer, it doesn't need to output a well-formed date string. That can happen in the mentioned presentation layer itself.

I disagree though slightly with the use of INT(17) columns, DATETIME seems more appropriate to me because you can use SQL's date arithmetic functions directly on it and don't need to convert the integer to a date type previously.

Thanks again.

01-26-2003, 05:49 PM
I dont think theres a right or wrong answer ?

I use TIMESTAMP(14) in MySQL as I like the mysql date manipulation that gives you (as with DATETIME) but I like the fact that it does the automagic thing :D

outside of MySQL I find it easier to work with a unix timestamp for all the reasons above.

anyway my reason for posting (since I said nothing of use;)) was about the `year 2037 bug` - will that dissapear with 64 bit ? or has that got nothing to do with it ? & if it has , how long does 64bit give us till we have to start worrying again?

01-26-2003, 06:59 PM
I think it depends on how the UNIX timestamp is stored internally in the C libraries, and currently I think it's a 32bit integer. Changing that to 64 bit and recompiling your app/the libraries should ensure timestamps for eternity:

64-bit UNIX time would be safe for the indefinite future, as this variable won't overflow until 2**63 or 9,223,372,036,854,775,808 (over nine quintillion) seconds after the beginning of the UNIX epoch - corresponding to GMT 15:30:08, Sunday, December 4, 292,277,026,596 C.E. This is a rather artificial and arbitrary date, considering that it is several times the average lifespan of a sun like our solar system's, the very same celestial body by which we measure time.

Quoted from: http://www.deepsky.com/~merovech/2038.html

Of course problems with the year 2038 could arise right now, think of a program that calculates your pension income once you start retiring. And if that program needed some date/time manipulation, it could fail for me because right now it seems I'd have to work long past 2038 before retiring... *grumble*

01-26-2003, 07:11 PM
GMT 15:30:08, Sunday, December 4, 292,277,026,596 C.E

bummer I was gonna be on holiday that weekend ... guess I will have to cancel ;)

01-26-2003, 08:11 PM
Originally posted by firepages
GMT 15:30:08, Sunday, December 4, 292,277,026,596 C.E

bummer I was gonna be on holiday that weekend ... guess I will have to cancel ;)

...and I bet you only booked the hotel this far in advance to gain the 'early bookers discount' :rolleyes: