MySQL - Combine two fields to create a unix timestamp?
I'm trying to retrieve a UNIX timestamp from a query by combining a date and a time field in the table, however it keeps returning as zero.
SELECT *,
UNIX_TIMESTAMP(startdate starttime) AS start,
UNIX_TIMES开发者_StackOverflowTAMP(enddate endtime) AS end
FROM mytable;
Can anyone help me out?
Thanks.
Not tested but it should work:
SELECT UNIX_TIMESTAMP(CONCAT(startdate, ' ', starttime)) AS start;
Look at this:
mysql> select TIMESTAMP(curdate(), curtime()), TIMESTAMP(curdate(), curtime()) + 0;
+---------------------------------+-------------------------------------+
| TIMESTAMP(curdate(), curtime()) | TIMESTAMP(curdate(), curtime()) + 0 |
+---------------------------------+-------------------------------------+
| 2010-04-21 19:03:23 | 20100421190323 |
+---------------------------------+-------------------------------------+
1 row in set (0.00 sec)
I am sorry, some addition:
mysql> select UNIX_TIMESTAMP(TIMESTAMP(curdate(), curtime())), UNIX_TIMESTAMP();
+-------------------------------------------------+------------------+
| UNIX_TIMESTAMP(TIMESTAMP(curdate(), curtime())) | UNIX_TIMESTAMP() |
+-------------------------------------------------+------------------+
| 1271862564 | 1271862564 |
+-------------------------------------------------+------------------+
1 row in set (0.00 sec)
精彩评论