MySQL - Combine two fields to create a unix timestamp?

Question

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_TIMESTAMP(enddate endtime) AS end
            FROM mytable;

Can anyone help me out?

Thanks.

Ionuț G. Stan · Accepted Answer · 2010-04-21 14:57:25Z

4

Not tested but it should work:

SELECT UNIX_TIMESTAMP(CONCAT(startdate, ' ', starttime)) AS start;

answered Apr 21, 2010 at 14:57

Ionuț G. Stan

180k19 gold badges196 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

newtover · Accepted Answer · 2010-04-21 15:10:16Z

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)

Collectives™ on Stack Overflow

MySQL - Combine two fields to create a unix timestamp?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related