Unix_timestamp()

UNIX_TIMESTAMP()
I have a timestamp field and the date & time appear fine in it through phpMyAdmin, however when I do a SELECT UNIX_TIMESTAMP(field), it returns a unix timestamp for me to use in PHP but it is in the future. Any ideas why or how I can fix this?

---

Unix_timestamp()
I have columns called repeat_date and repeat_month and I would like to
use the info within them in my WHERE clause as part of a
UNIX_TIMESTAMP().

Essentially, I would like to say something like:

SELECT * FROM table WHERE
UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) > $date_start
AND UNIX_TIMESTAMP(repeat_date-repeat_month-$curYear 0:00:00) <
$date_end

The variables $curYear, $date_start and $date_end will have been set
previously with PHP.

I'd like to use the information in each row within the
UNIX_TIMESTAMP().
Is this possible? If so, how would I do it?

---

Newb: Can You Do This With UNIX_TIMESTAMP?
Is this valid:

UNIX_TIMESTAMP(DATE_ADD(now(), INTERVAL -15 MINUTE)) ?

---

Using Unix_timestamp Function
Can anyone see anything wrong with the way I am using the MySQL unix_timestamp function in this snippet from a query?

$importdata="INSERT INTO import
(date, gluc, humilog, regular, lantus, carbs)
VALUES (unix_timestamp('2004-08-01 05:04:00'),152,02.0,00.0,00.0,00.0)

The table column where I am inserting this was created as:

"date timestamp not null"

I am getting an incorrect timestamp value (20001228122240)that converts to "Mon, 18 Jan 2038 20:14:07 -0700" in PHP. As far as I can see, my usage of unix_timestamp is the same as that in the MySQL manual.

---

Getting Records When Using UNIX_TIMESTAMP
I'm currently using this query:

SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC LIMIT 0, $nrpp

My question is... can I pull all records where the converted "date_time" is > a certain time?

I tried the below but it didn't seem to work:

$time = time();

SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys WHERE UNIX_TIMESTAMP(date_time) > '$time' ORDER BY date_time ASC LIMIT 0, $nrpp

Btw... I'm using PHP as my server-side language.

---

MySQL Unix_timestamp
I have a MySQL query used to compute the total time a device is within a expected location. The loglocations table contains a history of every device's location history within the DB.  the starttime/endtime are datetime fields which represent how long a device is in a partcular location for that time duration. The macaddress is the ID of the device and is comes from the loghistories table.  Both tables are linked by the historyId and uuid respectively:

SELECT (SUM(UNIX_TIMESTAMP(ll.endtime) - UNIX_TIMESTAMP(ll.starttime)) AS DEVICE_TIME FROM loghistories lh INNER JOIN loglocations ll ON (lh.uuid = ll.historyId) WHERE lh.macaddress = ? and ll.locationName = ? and ll.starttime >= ? and ll.endtime <= ?

all the ? marks represent user-inserted information.  This query works fine except for in one instance: Some devices have a 'NULL' endtime because the endtime of the device has yet to be determined (still in same location).  This causes the sum section of the query to equate to NULL which is of no use to me...is there any way I could somehow append the current datetime value (using NOW() function for instance) for all devices which contain a 'NULL' endtime??

---

Simple Question About UNIX_TIMESTAMP
UNIX_TIMESTAMP (fieldname) as fieldname
to work, does fieldname have to be a time or timestamp defined field?

Or, is it okay to have a text field as long as the date inside is formatted correctly?

---

What Is Wrong Here With FROM_UNIXTIME + UNIX_TIMESTAMP
I am using gentoo-AMD64 and the MYSQL server version provided by gentoo is currently 4.0.24

When I am inserting a DATETIME which is in that magic gap during the change from summer time to normal time, MySQL adds a magic hour. My bug, or a MySQL bug?

This is what I am doing:
######################################
mysql> create table a (f1 DATETIME);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a VALUES (FROM_UNIXTIME(1130631910));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+---------------------+
| f1 |
+---------------------+
| 2005-10-30 02:25:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP(f1) from a;
+--------------------+
| UNIX_TIMESTAMP(f1) |
+--------------------+
| 1130635510 |
+--------------------+
1 row in set (0.00 sec)
######################################

I inserted 1130631910 and later retrieve 1130635510?

---

Convert From UNIX_TIMESTAMP To Formatted Date
I'm holding a UNIX_TIMESTAMP (say, 1110715984) and I would like a way of formatting this in MySql into a date and time.

---

Convert Unix_Timestamp To DateTime In A Whole Column
I have an old table with a column:

datum varchar(30)
with Unix Timestamps in it.

Now I want to convert it into a new column with DateTime (0000-00-00 00:00:00).

Only have found the syntax to make it the other direction
UPDATE tabelle SET tmpDate = UNIX_TIMESTAMP(DATE_FORMAT(myDateTime,'%Y-%m-%d %T'))
WHERE myDateTime IS NOT NULL

Please can you give me the syntax to convert the whole column from Unix Timestamp to DateTime without loosing my dates.

---

Can You Use "UNIX_TIMESTAMP" Outside The Mysql Query?
Hey guys. I was using this code:

mysql_connect($dbhost, $dbuser, $dbpassword) or die("Couldn't connect to server.");
@mysql_select_db($dbname) or die("Couldn't connect to database.");

$q="SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC";
$sql=mysql_query($q);
$num=mysql_num_rows($sql);

mysql_close();

Then using this code to get the results:

while (list($poker_room,$game_type,$limit_type,$dt,$dt2,$buyin_desc,$description,$url)=mysql_fetch_array($ sql)) {
// Blah
}

But I need to only display certain results so depending on the page they are on.. so I was wanting to get each results values with the "mysql_result()" funtion; but I can't figure out how to get the UNIX_TIMESTAMP to work this way.....

---