confused on relativity of storing dates in mySQL

storing dates in mySQL

[elbowlobstercowstand]

I am designing a national job board application (PHP/mySQL). People can post jobs and the jobs expire after x amount of days. This part works just fine, as I store an expiration date (using mysql statement: DATE_ADD(NOW(), INTERVAL $posting_duration DAY)) and then only display those jobs that aren't expired.

But here is where the confusion kicks in and I'm so confused I'm not even sure how to go about asking it, so feel free to read between the lines:

What are the best practices for storing dates? Should they be stored with the user's local time? But then what if the posting client travels from east to west? Will that buy them 3 hours of posting time? The bigger concern is if the mySQL server is located in the east coast, and someone is trying to check on a job they posted in the west coast b4 a cuttoff date, their post might say it has expired (relative to the east) when in the west they actually should have 3 hours left. And that might frustrate them.

I might be totally missing something here and I am new to this. My brain hurts. Forgive me. Dates confuse me. I did ok with human dates, as I am married, but calendar dates, dang ... thanks in advance for the help!

PS: I have a feeling this text from the mySQL manual is key (that I use CURRENT_DATE as opposed to NOW), but alas, my peanut brain cannot currently decode it's techie jargon:

The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the connection's current time zone, which is available as the value of the time_zone system variable. In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the current time zone.

So if you'd like to contrast NOW() and CURRENT_DATE(), feel free. Again, many thanks and lobster dinners to you!

[mikesmith76]

I recently did something similar, a simple classifieds section for a website, and found it easier to store the time the classified was posted (using the php time() function). This is stored in MySQL in an INT field

When the query to pull the classifieds is run all that is needed is a simple addition to this time field, since it is an integer. You can then format the date however you please using date()

[txbakers]

I use dates in the DB formatted as yyyy-mm-dd. When ever someone sends a date to the DB I have to make sure it is formatted correctly for insertion.

I only use the TimeStamp as integer for giving me a unique record locator to match against another table.

Dates are indeed confusing. You can'd do simple addition on dates because of the 30/31/28 day thing. mySQL has lots of date functions to add/subtract and extract date parts. It was difficult at first, but I grew to like it very much.

[elbowlobstercowstand]

Thanks mikesmith76 and txbakers, however, I think I was a little unclear (as confusion tends to do that). I am not having problems actually storing the dates, or the field type to use . . . I am concerned with the time zone issue . Do you think I should be inserting the dates a) first correcting for the client computer's time zone (if I can even do that) or b) just store it raw relative to the server time zone? My original post alludes to why I am struggling over this:

"The bigger concern is if the mySQL server is located in the east coast, and someone is trying to check on a job they posted in the west coast b4 a cuttoff date, their post might say it has expired (relative to the east) when in the west they actually should have 3 hours left."

PS: I see that webmasterworld stores the times as UTC and the displayed posting datetime (top right) definitely does not match with my current datetime here in Arizona. So maybe a better question is how does webmasterworld do it? Shall I follow the masters (sorry, that was a horrible pun)?

[txbakers]

i store the dates and times as PST - the location of the server.

Each user sets their time zone as a preference, and then I do the time math to display their time/date.

[elbowlobstercowstand]

Thanks so much txbakers for your valuable time. It helps to know how other people handle this issue. I just changed my settings and saw the adjustment in action. Pretty sweet.