Timestamp problem...

evo4ever

Hey all! Im having trouble parsing a Unix Timestamp. This is the scenario...

I have a script which inserts news posts. When mysql recieves this news it creates it an ID from auto increment and creates a Timestamp using the NOW() function. On my news.php page I have the following line which parses the Timestamp into the following format...

print date("d.m.y @ H:i:s", $row["date"]);

The problem is that the date() function isnt displaying the correct date, it displays:

19.01.38 @ 03:14:07 (as most recent post)

Help appeciated. Thanks,

python_q

NOW() returns the time in this format: YYYYMMDDHHMMSS

I believe UNIX_TIMESTAMP() will return a UNIX timestamp that you can use with the date() function.

You can also use strtotime() to get a UNIX timestamp from the NOW() function.

evo4ever

Hmm, when I edited the line to:

print date("d.m.y @ H:i:s",   strtotime($row["date"]));

I got a warning saying:

"date(): Windows does not support dates prior to midnight (00:00:00), January 1, 1970"

How can I make mysql use UNIX_TIMESTAMP instead of NOW as default? Thanks.

Moonglobe

get it with TIMESTAMP(NOW()). just checked the reference manual @ mysql.com, took me all of five minutes.

evo4ever

Thanks. How do I apply "TIMESTAMP(NOW())"?

Moonglobe

in the SQL query, where else.

evo4ever

just to clarify:

"INSERT INTO table (date) VALUES ('TIMESTAMP(NOW())')"

Is that the correct syntax?

Moonglobe

take out the quotes around the functions and it is 🙂

evo4ever

I was kind of hoping mysql to handle the timestamp automatically, so I could omit the 'date' row when making INSERT queries. Is this possible in my position?

PS: im using phpmyadmin.

python_q

I believe TIMESTAMP() still returns "2003-12-31 00:00:00". To get an actual UNIX timestamp, try UNIX_TIMESTAMP(NOW()).

Moonglobe

what do you mean leave out the date line? the PHP function? when you SELECT you can use the DATE_FORMAT() function in mysql to get what you want. look it up on mysql.com

Moonglobe

my bad, python_q is right.

evo4ever

The field 'date' in my 'news' table is of type 'TIMESTAMP' so therefore I don't need to explicity send a timestamp to mysql because mysql handles this automatically when a new insert query is made. But when mysql uses TIMESTAMP it uses the NOW() function by default, how do i change this in phpmyadmin? Thanks.

Moonglobe

take a look here. timestamps are updated automatically on insert, so you can just not specify that field and it will default to NOW(), then when you SELECT, you can use eitehr DATE_FORMAT or UNIX_TIMESTAMP and date() in PHP to get the format you want.

hth
moon

evo4ever

Thanks. I've got this working instead:

I altered the date field to type 'datetime' and im using php to parse the it with strtotime(). It works now thanks for all your help 🙂

python_q

Also, you can just change the date field to INT(10) then whenever you want to insert the time just use UNIX_TIMESTAMP() in the query, like so:

INSERT INTO database ( date , text )
VALUES (
UNIX_TIMESTAMP( ) , 'text'
);

evo4ever

Thanks python_q, im using your method, and it works a treat 🙂

portaloo

always use mysql to format the date, always use datetime
str_to_time just wastes time.

INSERT INTO table (date) VALUES (NOW())
bugger timestamp let mysql do the work

$sql = mysql_query("SELECT Name, DATE_FORMAT(VisitTime, '%m.%d, %H:%i:%s') as vtime FROM STATS");
$query = mysql_fetch_array($sql);

other options
%Y: Long year (2002)
%M: Long month (August, September)
%m: Numeric month (08, 09)
%d: Day of the month (01)
%W: Long day of the week (Monday, Tuesday)
%a: Short day of the week (Mon, Tue)
%H: Hour (01, 02)
%i: Minute (01, 02)
%s: Second (01)

echo $query["vtime"];

there's my 2 cents worth