to insert DATE TO DB/TABLE, how must inserted the date , what format ?

lse123 · #1 (**permalink**) 10-19-08, 13:46

When in phpMyAdmin I have

Field Type
dateplaced timestamp

then in a command like

Code:

	$datenow = time();	 
	//  $datenow = date("H:i j/m/y", $tsnow);  // p.516-7
	$status="PENDING";
	$email6=$_SESSION['email'];
	$query = "INSERT INTO $BookingsTable VALUES(NULL,'$datenow',0,'$email6','$status','$datep','$dated','$Locationp','$Locationd','$days','$mostdaysrate','$vat','$off','$finalprice','$cat')";

to insert DATE TO DB/TABLE, how must inserted the date , what format ?
I insert timestamp but appears(in BROWSE DATA

ELECT * FROM `bookings2` LIMIT 0 , 30)
0000-00-00 00:00:00 as field dateplaced ??? well, why appear zeros ?

healdem · #2 (**permalink**) 10-19-08, 14:49

Im confused here
..do you men PHPMyAdmin or PHP
timestamp is a reserved word
you only need to encapsulate text / string columns
usually dates should be in ISO format (yyyy/mm/dd @ hh:mm:ss)
the @ hh:mm:ss is optional

http://www.google.co.uk/search?hl=en...e+Search&meta=

lse123 · #3 (**permalink**) 10-19-08, 15:13

I mean PHPMyAdmin.

Field = dateplaced // [1] '$datenow'
Type = timestamp

I try insert number $datenow = time(); to second column, that i guest is a big number, but appears when i browse data as 0000-00-00 00:00:00 and NOT today's date , well ?

healdem · #4 (**permalink**) 10-19-08, 15:32

this

Code:

$datenow = time();	 
	//  $datenow = date("H:i j/m/y", $tsnow);  // p.516-7
	$status="PENDING";
	$email6=$_SESSION['email'];
	$query = "INSERT INTO $BookingsTable VALUES(NULL,'$datenow',0,'$email6','$status','$datep','$dated','$Locationp','$Locationd','$days','$mostdaysrate','$vat','$off','$finalprice','$cat')";

looks like PHP to me. Im not aware you can run scripts like that in PHPMyAdmin

r937 · #5 (**permalink**) 10-19-08, 21:16

the format for mysql is 'yyyy-mm-dd hh:mm:ss' -- no @

lse123 · #6 (**permalink**) 10-20-08, 00:46

Please note: I want to keep track date/time , placed the booking, pickup date(car rent) and, dropoff date...I set these all fields to timestamp ... to save a date in db/table how I must insert date in what format(a big number) ? or what ?
If I insert a big number to timestamp field this will show a date like
'yyyy-mm-dd hh:mm:ss'
in the table: "browse command" in phpmyadmin(mysql) ?

r937 · #7 (**permalink**) 10-20-08, 00:50

please do a SHOW CREATE TABLE for your table

healdem · #8 (**permalink**) 10-20-08, 02:40

timestamp is a variant of datetime.. its set whenever the row is added or changed. so if you try to assign a value to a time stamp column you may have problems. if you have multiple timestamp columns they should all be set to the same value

use datetime datatype in place

lse123 · #9 (**permalink**) 10-22-08, 13:27

I try insert number $datenow = time(); to second column, that i guest is a big number, but appears when i browse data as 0000-00-00 00:00:00 and NOT today's date , well ?
0000-00-00 00:00:00 == A timestamp field to appear this what must be inserted ?

in my case I want to use time/date and compare it, appear only not expired bookings, sort by date etc ,... what to use ? what field type to use ?

r937 · #10 (**permalink**) 10-22-08, 13:31

use DATETIME datatype

make sure you insert an actual datetime value, not a unix integer

lse123 · #11 (**permalink**) 10-25-08, 15:40

WHEN I take current time from
$datenow = time();
how insert in DATETIME datatype ?

healdem · #12 (**permalink**) 10-25-08, 16:12

$DATENOW = format("Y-m-d H:i:s"); //FORMAT THE DATE IN MySQL form
$SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, $DATENOW);";
$sqlr=@mysql_query($SQL,$cnn) or die (echo ("myerrormessage".mysql_error());

$cnn is the variable containing a valid MySQL connection

r937 · #13 (**permalink**) 10-25-08, 16:47

why generate the value in php and pass it to the database?

why not use the database function?

so instead of this --

$DATENOW = format("Y-m-d H:i:s"); //FORMAT THE DATE IN MySQL form
$SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, $DATENOW);";

you would run only this --

$SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, CURRENT_TIMESTAMP);";

lse123 · #14 (**permalink**) 10-26-08, 11:17

DB field type: timestamp, HOW IS BEING SAVED AND APPEARED ? SAVED AS BIG INT ?

healdem · #15 (**permalink**) 10-26-08, 11:28

what does it matter how it is stored.. its a variant of a datetime column, one you cannot set yourself. think of it as a read only date time.

it gets changed every time there are changes applied to that row.

there are functions to manipulate that datatype both in MySQL and PHP.
http://bugs.mysql.com/bug.php?id=24895

it looks lke the datetime datatype is an 8 byte value, I'd expect it to be an integer being he number of clicks since an arbitary date (probably the Unix Epoch). you shouldn't be any direct mathmatical manipulation on any date time values, use the presupplied functions.

Internet Services

Web Development

Digital Marketing

Consumer Tech