Inserting Date into Table

[chronic]

Hey there,

I am wanting to insert a date into a table for the date something was inputted into the database. Which would be the best (or only) way to do that?

1. DATE function in MySQL
2. date() function in PHP and submit the output into MySQL

If there are any other ways, let me know, as I would like this to be the best possible way and work all the time.

[r937]

the best way is 1., except the DATE function is the wrong one

use the CURRENT_DATE function for DATE columns, and the CURRENT_TIMESTAMP function for DATETIME columns

[jondolar]

"INSERT INTO mytable SET thedate = NOW()"

Alternatively, you can create your table with a timestamp field. The first timestamp field is always set every time the record is created/updated. Might not be what you want.

If your database is on a different server than where the script is running and you want the script to determine the time then you will need to use php to generate the date/time

$thedate = date("Ymd");
"INSERT INTO mytable SET thedate = '$thedate'"

Good luck with your project.

[chronic]

Okay, if I want to keep my script so it will work on several different types of servers (such as different PHP/MySQL versions), is "INSERT INTO mytable SET thedate = NOW()" going to work with all the current versions out there?

[r937]

instead of "INSERT INTO mytable SET thedate = NOW()" (which is horribly non-standard), please use

INSERT INTO mytable (thedate) VALUES (CURRENT_TIMESTAMP)

[jondolar]

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

LOCALTIME and LOCALTIME() are synonyms for NOW().

NOW()

Now() Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

The INSERT syntax I used is also correct.

You can spend lots of time making sure your SQL statements run across all kinds of databases, or you can build your application.

My syntax is 100% mysql stanard and will work across any version of mysql on any host.

Good luck with your project.

[longneck]

You can spend lots of time making sure your SQL statements run across all kinds of databases, or you can build your application.

or option C: spend a little bit of time learning the standard syntax that works on your target server and use that, and avoid syntaxes that are known to be incompatible. it doesn't take that much effort and when your next client asks for a non-mysql application you don't have to reinvent the wheel or learn a bunch of new stuff.

[jondolar]

I agree with that

[r937]

The INSERT syntax I used is also correct.

oh, it's fine for mysql, but it is still non-standard

try to port your app to any other database and you will see what i mean

[longneck]

I agree with that

ok, then you also have to agree to (try) not use the INSERT/SET syntax and NOW().

[chronic]

Yeah, thanks for all the info!

Also, I use the following INSERT syntax already:

INSERT INTO table (field) VALUES ('value1')