mysql timestamp and php query

Question

I have a MySQL database. All the fields, I assign and I have a datestamp for my date field. it automatically generates YYYY-MM-DD HH:MM:SS like this 2011-11-21 21:31:37 However, I would like it to do so in two diffrent columns:

A date field with YYYY-MM-DD or 2011-11-21

A time field with HH:MM:SS or 21:31:37

This is my insert php code

$sql= "INSERT INTO  `db`.`table` (`id` ,`fkid` ,`paid` ,`date`)
VALUES (NULL, '$userid', '0', CURRENT_TIMESTAMP);";

I have tried CURRENT_DATESTAMP and it does not work.

The 2nd part of the question is: how to I make the table so it works with the proper code? Should the structure of the of the field be type text, or date?

Adam Wenger · Accepted Answer · 2011-11-22 02:53:10Z

5

I would not recommend splitting your timestamp into separate date and time columns. Instead, it is easiest to use a DATETIME column, and query it for its date and time portions using the MySQL functions DATE() and TIME():

SELECT DATE(`date`) AS d, TIME(`date`) AS t FROM db.dable;

When inserting, you can use the NOW() function to set the current timestamp. `CURRENT_TIMESTAMP() is a synonym for NOW().

$sql= "INSERT INTO  `db`.`table` (`id` ,`fkid` ,`paid` ,`date`)VALUES (NULL ,  '$userid',  '0', NOW());";

edited Nov 22, 2011 at 2:53

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

answered Nov 22, 2011 at 2:50

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cripto Over a year ago

How can I then compare the what d returns to the current date

Collectives™ on Stack Overflow

mysql timestamp and php query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related