7 replies to this topic

[faulk]

Hey all, I have a quick question about using the SQL function UNIX_TIMESTAMP I am using it in this way:

INSERT INTO `nate_faulk`.`gw_comments` (`id`, `type`, `rid`, `comment`, `time`) VALUES (NULL, \'\', \'\', \'\', UNIX_TIMESTAMP(\'2007-09-19 00:00:00\'));

However for some reason when I check the value this created it is always the default value (0000-00-00 00:00:00) I am using this on a datetime field and running MySQL 4.1 if any of the helps. I am not an SQL pro.. So I have no idea why it's doing this!

[curthard89]

it will be the default of that, when u insert u need to put the time in yourself with php in that format.

[faulk]

then I guess I don't understand why there's a SQL function UNIX_TIMESTAMP if it doesn't do anything. Correct me if I'm wrong but shouldn't it convert it on it's own?

[curthard89]

nope, its there so u can compare dates easily in php, any other format would be hard to compare, but u can string to time a unix time stamp and esily compare date and time that way.

[Hayden]

Unix time stamps show up like this

Quote

1189451850

Generally for inserting information into a database you would do

INSERT INTO `nate_faulk`.`gw_comments` (`id`, `type`, `rid`, `comment`, `time`) VALUES (NULL, \'\', \'\', \'\', NOW());

and it will grab the current time/date from the server and insert it.

when you pull it from the database later, you can use the date() function to format it into a more normal format.

[faulk]

I am interested in converting a normal date into a unix timestamp rather than just the current time. Here's the process I am performing (in phpMyAdmin)



Everything seems to be set up correctly for this to work, but you'll notice the end result is the fields default value. Am I using this wrongly? My purpose of converting a date to a unix timestamp is so I can manage dates from oldest to newest easier.

[Demonslay]

Um, yeah you are using it wrong.

UNIX_TIMESTAMP() is for converting from the kind of date that MySQL stores (2007-09-19 00:00:00 for example) into a Unix timestamp (1189451850 for example, as Spatial said). The converse of this, is using FROM_UNIXTIME(), which converts the Unix timestamp into MySQL's format.

In your code, you are essentially storing a MySQL formatted date, into a Unix timestamp, making it an invalid entry for a DATETIME column, thus making it goto the default value of 0000-00-00 00:00:00). You shouldn't be doing this. If you have it already in the format, you should leave it and store it that way. Then, if you need it as a timestamp for your PHP functions, use UNIX_TIMESTAMP() when pulling it out.

Examples. MySQL connection assumed.

<?php
$timestamp = time(); // Timestamp for right now

// Inserting - this would be more efficient using NOW()
// if we were doing the date of right now, but bear with me
mysql_query("INSERT INTO `table`(`timestamp`) VALUES(FROM_UNIXTIME({$timestamp}))") or die(mysql_error());
// It is now stored in the YYYY-MM-DD HH:MM:SS format for MySQL

// Selecting back as a timestamp for our PHP functions
$query = mysql_query("SELECT UNIX_TIMESTAMP(`timestamp`) AS time FROM `table`") or die(mysql_error());
$result = mysql_fetch_row($query);
// Now $result['time'] is equal to the Unix timestamp,
// and can be directly used in functions like date(), etc
?>

If you store dates in this way, you can use MySQL's date comparing functions for sorting and such. (More information).

[faulk]

thank you for clearing all of that up demonslay I understand it perfectly as well as what I was doing wrong. again thanks for the help, very much appreciated.