Whats the best way to store the date?

Elliott

Hi.

Im wondering what the best way to store the date in my database.

I will need to use the date to search for certain time frames....

eg all records with date between date 1 and date2 etc.

Im not too familiar with building my own search facilities, but i came up with an idea to store the date as date('Ymd') so i would get 20050419 etc.

Than i could simply do a maths equation.

Is there a standard way to store and search dates that anyone could point me to please.

Thanks very much
Elliott

Kudose

http://simon.incutio.com/archive/2003/07/11/storingDatesInMySQL

pohopo

just use the date datatype of the particular database you are using. some database have two date types. One with only the date and another with both the date and time stored. I personally like having both the date and time as it is more flexible.

Elliott

ok thanks guys.... been reading into it more.

Ive created a DATETIME field in my table....now ive read somewhere than when a new row is created, MySQL automatically fills in this DATETIME field.?

Is this true or do i have to assign it to a variable such as....

$regdate = date('Y-m-d H:i:s');

is that right?

also, how do i display just the date bit and not the time once ive pulled it from the DB.

dalecosp

I tend to hate DB date fields, and simply store UNIX timestamps. Different strokes for different folks, I guess...

how do i display just the date bit and not the time once ive pulled it from the DB., I guess...

Depends on what format it is. Are you familiar with [man]strtotime/man?

xblue

Mysql's DATETIME is not auto-updating. TIMESTAMP is, but it updates whenever the record is nodified, not only on insertion.

Instead of creating the date in php, you can use NOW() in you insert query to create the current datetime.

In order to extract the date part, there is the function
DATE(name_of_datetime_column) from version 4.1.1.

Elliott

thanks guys 😃

obviously still new to this, but i understand what u mean.

Thanks for the help