datetime function in php

Question

I want to store the data and time in my MYSQL db.I have a datetime field in my db I want to store current datatime in my db How shold i get the current date time?How to pass it to db via a sql query

How can i then retriev an print it in correct yyyy--dd--mm format or any other format What wil be format of time? wil it be 23 hrs etc? How do i print date and time?

Answer 1

You could use MySQL's built in date/time functions if you only want to insert it into the MySQL database.

Have a look at: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Otherwise, you can use the PHP's date() function.

Answer 2

You can let MySQL determine the current timestamp by using Now() in your query.

INSERT INTO foo (dtfield) VALUES (Now())

This can also be done with a default value for a TIMESTAMP field in your table definition

CREATE TABLE foo (
  id int auto_increment,
  creationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  v int,
  primary key(id)
)

Answer 3

You might also want to set your timezone.

Answer 4

You can look at VolkerK's answer for the SQL part, then you can use strftime http://ca.php.net/manual/en/function.strftime.php to format it the way you want. See PHP doc for more options.

echo strftime("%Y-%m-%d", $yourSQLTimeStamp);

Answer 5

Assuming table named 'items' and field named 'modified' of type 'timestamp'

$r = mysql_query("INSERT INTO items (modified, x, ...) VALUES (CURRENT_TIMESTAMP, $x, ...)");
// (or "UPDATE items SET modified=CURRENT_TIMESTAMP, x=$x, ...)
...

$r = mysql_query("SELECT UNIX_TIMESTAMP(modified) FROM items");
$item = mysql_fetch_assoc($r);
$formatted_ts = date('g:ia', $item['modified']); // or another format *

you'll need to add appropriate error-checking which I've omitted; also need to adjust for consideration of timezones, which I've also left out

• see date()