To get date and time, MySQL provides the following functions:
| Function | Description |
|---|---|
| NOW() | Returns the current date and time according to your computer's system date and time. |
| CURDATE() or CURRENT_DATE() | Returns the date in YYYY-MM-DD |
| CURTIME() or CURRENT_TIME() | Returns the current time in HH:MM:SS |
| CURRENT_TIMESTAMP() | Returns the current date and time in YYYY-MM-DD HH:MM:SS |
| DAYOFYEAR(date) | Returns days since the beginning of the calendar year. |
| DAYOFMONTH(date) | Returns the day of the month. |
| DAYOFWEEK(date) | Returns the day of the week, with Sunday being 1. |
| WEEKDAY(date) | Returns the day of the week, with Monday being 0. |
| MONTHNAME(date) | Returns the name of the month. |
| DAYNAME(date) | Returns the name of the day. |
| YEAR(date) | Returns the year. |
| QUARTER(date) | Returns the quarter. |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="EN" lang="EN">
<head>
<title>Date and Time Functions</title>
<meta Name="Author" Content="Hann So">
</head>
<body>
<p>
<?php
if (isset($_POST['submit'])) {
process_form();
}
else {
display_form();// display form for the first time
}
function display_form() {
echo <<<HTML
<h2>Date and Time Functions</h2>
<form action = "$_SERVER[SCRIPT_NAME]" method="post">
Username:
<input type="text" name="username" size="50" value="hann" />
<br />
Password:
<input type="password" name="password" size="50" />
<br />
Date (YYY-MM-DD):
<input type="text" name="date" size="50" value="2008-12-24" />
<br />
<input type="submit" name="submit" value="Submit" />
</form>
HTML;
}
function process_form() {
DEFINE ('DB_USER', "$_POST[username]");
DEFINE ('DB_PASSWORD', "$_POST[password]");
DEFINE ('DB_HOST', "localhost");
$date = "$_POST[date]";
echo "<p>Opening the connection to the database server.</p>";
if ($link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)) {
// we are connected
// use MySQL query to calculate an age in days
// define the query
$query = "SELECT CURDATE()";
// run the query
if ($res = @mysql_query($query)) {
$day = mysql_fetch_array($res);
// display
echo "The day is $day[0]<br />";
}
else {
die ("<p>Could not run query because: ". mysql_error(). "</p>");
}
// define the query
$query = "SELECT MONTHNAME('$date')";
// run the query
if ($res = @mysql_query($query)) {
$day = mysql_fetch_array($res);
// display
echo "The month is $day[0]<br />";
}
else {
die ("<p>Could not run query because: ". mysql_error(). "</p>");
}
// close the connection
mysql_close($link);
}
else {
die ("<p>Could not connect to MySQL because: ". mysql_error(). "</p>");
}
echo "<p><a href=\"$_SERVER[SCRIPT_NAME]\">Try again?</a></p>\n";
}
?>
</p>
</body>
</html>
|