PHP date/time FAQ: How do I create a date in the proper format to insert a SQL Timestamp field into a SQL database?
First off, you may not need to create a date in PHP like this. If you're using plain old PHP and a database like MySQL, you can use the SQL 'now()' function to insert data into a SQL timestamp field, like this:
INSERT INTO projects (user_id, name, last_updated, date_created) VALUES (5, 'alvin', now(), now());
I just tested this with PHP and MySQL, and it works fine. So if all you really needed to know was how to populate a SQL timestamp field in a SQL INSERT query, that's probably the easiest way to go.
However, if you want to do this all in PHP (or need to, depending on what framework you're working with), you can get the current date and time in the proper format using just PHP, like this:
$timestamp = date('Y-m-d G:i:s');
If you print this out, your $timestamp field will now contain contents like this:
You can then use this formatted timestamp string in a PHP MySQL insert.
Although this isn't a standard, off the shelf PHP/MySQL insert statement, here's what a SQL INSERT query looks like when I use this with Drupal 7:
$project = new stdClass(); $project->user_id = get_user_id(); $project->project_count_type = $form_state['values']['type']; $project->name = $form_state['values']['name']; $project->description = $form_state['values']['description']; # get the current time in the proper format for a sql timestamp field $timestamp = date('Y-m-d G:i:s'); # new drupal 7 style insert $id = db_insert('projects') ->fields(array( 'user_id' => $project->user_id, 'project_count_type' => $project->project_count_type, 'name' => $project->name, 'description' => $project->description, 'last_updated' => $timestamp, 'date_created' => $timestamp )) ->execute();
As you can see in the lines I've made bold, I'm inserting my timestamp variable into two SQL fields.
Note that the PHP date function defaults to the current date and time. This is exactly what I need for my purposes here. If you need to create a formatted timestamp field for some other date and time, you can do that something like this:
$timestamp = date('Y-m-d G:i:s', mktime(0, 0, 0, 7, 1, 2000));
Here are some other mktime examples:
$tomorrow = mktime(0, 0, 0, date("m") , date("d")+1, date("Y")); $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y")); $nextyear = mktime(0, 0, 0, date("m"), date("d"), date("Y")+1);
I pulled those examples from the PHP date page. Please see that page for more information on creating other dates and times (I'm mostly just worried about "now" at this moment).
I hope these timestamp examples have been helpful. As you've seen, you can generally just use the SQL 'NOW()' function to insert into a SQL timestamp field, but if that doesn't work for some reason, you can also create a timestamp field in the proper format using just PHP and the date function.