Timestamp help

[Adrian Schneider]

A few questions with Linux timestamps.

How do you insert them into mySQL, using time(), date() or just an empty row?

My timestamps are looking like this: 20050302171707 - while this looksaccurate seeing as how it is 2005 - 3rd month - 2nd day, my friend saysthat is completely inaccurate. He then pointed me to this link (http://corz.org/public/scripts/php/source/corz%20function%20library/unix-timestamp-to-date.php?timestamp=20050301194304&realtime=) to proove himself correct.

So what's the deal....:ermm:

[sabret00the]

to insert a timestamp into SQL you need to escape it as with any variable really but just use '" . TIMENOW . "' that's the vB version (pre escaped for ya)

[Adrian Schneider]

Thanks, I'll give this a shot.

So is the timestamp I am currently getting a crazy coincidence? Or just a unusable timestamp?

[filburt1]

Your timestamp is formatted with separate fields. A UNIX timestamp (seconds since January 1st, 1970) is only a scalar value.

[Adrian Schneider]

Well I tried your suggestion - and read a thread about it (http://www.webdesignforums.net/showthread.php?t=8820&highlight=unix+timestamp)on filburts website - no luck yet. I checked the field type andit is TIMESTAMP(14). I tried '" . TIMENOW . "' with and withoutthe single quotes, I get different values for each. I even triedinserting the value a few ways into phpMyAdmin (even the UNIX_TIMESTAMPfunction) - still got the number below.

I think with the single quotes I'd get no value returned (000...) and without I'd get 20001112070437.

Any more info needed to sort this out let me know. It's really buggin me.:ermm:

[sabret00the]

you can just use time() if you're running the query straight but you'll even need to escape that

here's an example of entering this query
$DB_site->query("
INSERT INTO table(userid, time)
VALUES ($bbuserinfo[userid], " . TIMENOW . ")
");

ooops i guess i didn't use the apostraphies, oh well i know that works :)

[Adrian Schneider]

Query:

$creategame = $DB_site->query("
INSERT INTO fc_game (gamename, creator, gametype, matches, datecreated) VALUES ('$gamename', '$creator', '$gametype', '$matches', ". TIMENOW .")
")

Which Returns:
20001112072628

:ermm:

[sabret00the]

that be the case what's the problem?

[Adrian Schneider]

converted "20001112072628" to..
19 January 2038 03:14:07 AM

^^ problem, unless I've been asleep for a few years

[sabret00the]

ooooooh, major crash and burn

you sure the time is correct on your server?

[Adrian Schneider]

I guess that is a possibility, however vBulletin's time is accurate, so I'm assuming that isn't the problem.

[filburt1]

Ensure that the format you're using for storing the date/time is INT(10) or INT(11), not TIMESTAMP or whatever the other time column format is.

[Adrian Schneider]

Oh that would probably do it, I was told to use TIMESTAMP(14). I'll try that out.

filburt1 - I love you. :P