Tweet
Creating date and time fields
It is very common to want to associate a date and/or time with a row’s worth of data. For instance, your table rows might represent requests made by your Web site users, and the associated date/time is the time that that request hit your database.
Now, one way to insert or update date fields is to include a string that represents the desired date in a format parsable by your database. For example, if you want to set the mydate date time field of all rows of my table to a particular date, you might set up a query like this one:
and then send that query off for evaluation. (Unfortunately, the exact standards of readable date formats vary quite widely from one SQL database system to another. This particular date string means November 24, 2003, as far as MySQL is concerned.)
The preceding approach is fine, as long as you take care that the particular date string you send is, in fact, readable as a date by your DB. Things get more complicated if you need to
construct such a string on the fly to represent a date that depends on the value of variables in your script.
The main thing to remember is that, with most database systems, there is no need to go through such contortions to set a field to the current date or time. Many have a current-date function that can be embedded directly in your query. For example, a MySQL version of the preceding query that sets the relevant date/time field to the current instant looks like this:
Note that the call to now() is not enclosed in single quotes, because it’s a call to database function rather than a string to be interpreted by the database as data. The analogous query
for Microsoft SQL Server looks like:
✦ date_add(date, date-interval)
✦ date_sub(date, date-interval)
Here date-interval is a string that includes a number of time units and the type of unit.A MySQL query to set all rows to a time a week from now might look like this:
Now, one way to insert or update date fields is to include a string that represents the desired date in a format parsable by your database. For example, if you want to set the mydate date time field of all rows of my table to a particular date, you might set up a query like this one:
$query = “UPDATE mytable SET mydate = ‘2003-11-24’”;
The preceding approach is fine, as long as you take care that the particular date string you send is, in fact, readable as a date by your DB. Things get more complicated if you need to
construct such a string on the fly to represent a date that depends on the value of variables in your script.
The main thing to remember is that, with most database systems, there is no need to go through such contortions to set a field to the current date or time. Many have a current-date function that can be embedded directly in your query. For example, a MySQL version of the preceding query that sets the relevant date/time field to the current instant looks like this:
$query = “UPDATE mytable SET mydate = now()”;
for Microsoft SQL Server looks like:
$query = “UPDATE mytable SET mydate = getdate()”;
✦ date_sub(date, date-interval)
Here date-interval is a string that includes a number of time units and the type of unit.A MySQL query to set all rows to a time a week from now might look like this:
$query = “UPDATE mytable SET mydate = date_add(now(), INTERVAL 7 DAY)”;
Comment