storing dates in mysql

ChicoMendez

hi

im developing an application and at the moment in my details table i have a dateadded field which it in fact an int(11) field.
however i would imigine that it would be best to store dates as a DATE field ? thoughts ? it would be easier to do selects on specific date criteria?

basically i have already a few hundred records in that table... does anyone know MySQL command to change the field type and change all the actual dates across at the same time so i dont have to enter them all again??


Thanks

Surrender

From what I remember theres a DateTime variable, displays it like 'YYYY-MM-DD HH:MM:SS' .Try the link below it covers Date, DateTime and Timestamp hope this is helpfull
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

seamus

I'm not sure if MYSQL will successfilly convert the int timestamp field into a proper date.

If it won't what you could do is:
1. Create a new empty table that's identical to the current one, except that the date field is a DATETIME.
2. Write a php* script: This SELECTS all of the rows from the current table, and builds a new INSERT statement for the duplicate table, converting each dateadded int into a DATETIME using the php date() function.
3. When the insert statement is built, execute it on on the new table. Verify your data is correct.
4. Drop the original table, and rename the second table to the first's name.

Of course, watch out for dependencies on the original table and all that guff.

* You don't have to use PHP. Other languages may work just as well

Rollo Tamasi

a while ago i had to convert 3 int fields into a date field. I know it's not exactly what you want to do but this code should give you an idea of how to go about constructing a query. The value eventdate is a date varibale.

[PHP]$eventdate = date("y-m-d", strtotime($_POST."-".$_POST."-".$_POST));
$sql = "INSERT INTO concert (eventdate) VALUES ('$eventdate')";
$query = mysql_query($sql) or die("Cannot query the database.<br>" . mysql_error());
echo "Database Updated."; [/PHP]

How is your date been outputted? Is it 02062006 ddmmyyyy? You could write a function which inserts - between the 2rd and 3rd character and the 4th and 5th and so forth and then insert that functions variable into the database as a date variable using the default 00-00-0000.

I wouldn't know exactly how to do it. Try googling de-pagnation or something? mmmm....maybe not. I don't know the term you would give to that tbh. Splitting a variable by char length or something....probably

Rollo Tamasi

i looked into this, you're going to have to use the str_split function to split your int into smaller variables

The code below should print out the first and second character of the variable $date_int.

[php]
<?php

print_r(str_split('$date_int',1,2));

?>
[/php]

And then you can use the code i have above to put them in a date form and then enter them into the database.

Serbian

There is a much easier way of doing this. Create a new field in the table you want to store the date and set it as DATETIME. Execute the following SQL Statement (updating it to the details of your database):

UPDATE [I]TableName[/I] SET [I]DateTimeField[/I] = FROM_UNIXTIME(dateadded)

This of course assumes that your DateAdded field stores the dates as Unix Timestamps.