timestamp

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rkgordon3
Forum Newbie
Posts: 6
Joined: Tue Mar 14, 2006 10:52 pm

timestamp

Post by rkgordon3 »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I am attempting to insert records into a simple mysql table that looks like:

Code: Select all

create table accepts (
id int not null auto_increment,
user varchar(9) not null,
time timestamp not null,
primary key(id)
);
My php looks like:

Code: Select all

$db = mysql_connect("localhost", "root");
     $ts = time();
     mysql_select_db("confirmations", $db);
     $sql = "insert into accepts (user, pin, time) values ('" .
                             $_REQUEST['user'] . "', '" .  $_REQUEST['pin'] . "', " .  $ts . ")";
    
     $result = mysql_query($sql);
My inserted records look like this:

Code: Select all

mysql> select * from accepts;
+----+------+--------+----------------+
| id | user | pin    | time           |
+----+------+--------+----------------+
|  1 | rkg  | 123123 | 00000000000000 |
|  2 | rkg  | 123123 | 00000000000000 |
|  3 | rkg  | 123123 | 00000000000000 |
I am *ALWAYS* getting 0s in time field. This, in spite of an echo'd
$sql string that looks quite right--with a non-zero int value for timestamp
(That looks like about the right number of seconds since Jan 1, 1970. :)

I am new to php but have been doing the same in Java for years. I must
be missing something obvious.

Any hints?

Thanks, Rob


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the timestamp type is not a unix timestamp. It's a textual string in the date() format of YmdHis .. if you do not set it, it will be set for you (if memory serves) .. it will also be updated each time the record is updated automatically.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

I really need to learn to type faster ... :oops:

It a TIMESTAMP ...
From: http://dev.mysql.com/doc/refman/4.1/en/ ... rview.html

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
No need to set a value for it as it's done automatically. Check out these time and date functions too.
rkgordon3
Forum Newbie
Posts: 6
Joined: Tue Mar 14, 2006 10:52 pm

Post by rkgordon3 »

I need to learn how to read faster.

I had just come to this conclusion.

Thanks.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You may also find http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html or use

more specifically UNIX_TIMESTAMP()
Post Reply