...

View Full Version : Right way to store time stamp?



verdafun
02-23-2010, 09:37 AM
A small clarification please.
is getting time in php as
time() and then storing in mysql database as datatype
TIMESTAMP is the best way to store time relevant info such as blog post etc?.

Dormilich
02-23-2010, 01:11 PM
a TIMESTAMP is sufficient, but you also could use DATE or DATETIME, what’s unnecessary is the time() call. MySQL brings its own time setting method, which you could simply call in your query.

INSERT
-- etc.
SET
`date` = NOW()

bacterozoid
02-23-2010, 02:37 PM
If you don't need to do advanced manipulation of your timestamp field with sql, just use an unsigned int...if you use one of the other data types then sql is going to convert it into some format you may not like...which you'll then have to most likely convert back. Unix timestamps are raw and simple and I always keep them that way.

hinch
02-23-2010, 06:08 PM
I stick with standard mysql NOW() function and mysql date format ie yyyy-mm-dd hh:mm:ss

its easy to work with and doesn't involve any insane maths like a unix time stamp does :)

MattF
02-23-2010, 06:42 PM
If you don't need to do advanced manipulation of your timestamp field with sql, just use an unsigned int...if you use one of the other data types then sql is going to convert it into some format you may not like...which you'll then have to most likely convert back. Unix timestamps are raw and simple and I always keep them that way.

Pretty much my opinion too. Plus, you know that dumping a time() value in an int field is going to work with any DB type. No arsing around trying to find out what syntax a specific DB maybe using for a time function. Unix timestamp can also be formatted in any style on output too, so you ain't stuck with some format you can't easily change at some point down the line.

verdafun
03-01-2010, 02:02 PM
Thanks for the suggestion.
This is slightly out of topic, what is the best way to format it and show it the user - php or directly using mysql's format functions?

bacterozoid
03-01-2010, 02:07 PM
Think of your database as nothing more than a stupid storage engine. It can certainly do other stuff, but you don't want it to.

Use PHP's date() function to format the timestamp to your liking. That way, if you ever need to change database types, you're not going to have to look for equivalent functions.

verdafun
03-01-2010, 02:18 PM
Think of your database as nothing more than a stupid storage engine. It can certainly do other stuff, but you don't want it to.

Use PHP's date() function to format the timestamp to your liking. That way, if you ever need to change database types, you're not going to have to look for equivalent functions.

Thanks again, especially for "stupid storage engine".
it is still unclear to me why one should use an unsigned int...wont that make years older than 1970 impossible to store?

Dormilich
03-01-2010, 03:03 PM
it is still unclear to me why one should use an unsigned int...wont that make years older than 1970 impossible to store?

you know that the unix time stamp starts at 01/01/1970 ?

bdl
03-01-2010, 03:35 PM
Using a UNIX TIMESTAMP (http://en.wikipedia.org/wiki/Unix_timestamp) is fine for a generic timestamp, as long as that's all you use it for, a unique identifier for that record. If you want to actually use that value to perform date calculations or actually store a date value, use a DATE type field (http://dev.mysql.com/doc/refman/5.1/en/datetime.html). Be sure you understand that the UNIX / POSIX timestamp values carry the limitation that they are only good for values between Jan 1, 1970 and Jan 19, 2038 (http://en.wikipedia.org/wiki/Year_2038_problem) (on 32 bit systems, which are most common).

Although using a UNIX TIMESTAMP with an INT type field is a more platform agnostic and portable way to store the date, I personally almost always use the MySQL TIMESTAMP type (http://dev.mysql.com/doc/refman/5.1/en/datetime.html) and set the default value to CURRENT_TIMESTAMP, so when you perform an insert on the table, it automagically sets the current date / time value for you, without even having to reference the field name. You can also set the ON UPDATE to update this value on any successive record updates. The plus side here is that this is a value that MySQL can use to perform any date calculation or manipulation, with the many DATE and TIME functions (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) that are inherent in MySQL. Want to convert this value to a UNIX TIMESTAMP? There's a function for that.

Your best bet is to check the MySQL reference manual (http://dev.mysql.com/doc/refman/5.1/en/index.html).

verdafun
03-02-2010, 08:55 AM
bdl : thanks, iam now using your idea as i feel it is best suggested here.

verdafun
03-02-2010, 09:48 AM
you know, the green text..

Dormilich
03-02-2010, 09:49 AM
Guidelines #6

abduraooft
03-02-2010, 09:50 AM
how do i set "resolve" to this thread?See the last item at http://www.codingforums.com/postguide.htm



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum