MYSQL CONCAT_WS and UNIX_TIMESTAMP

Question

Hello I have date and time table:

date               time 
09-03-2014  06:17:15
09-03-2014  06:23:06
09-03-2014  06:28:57
09-03-2014  06:40:39

I will make this:

SELECT CONCAT_WS(' ',date,time) AS newtime FROM hive96
newtime
09-03-2014 06:17:15
09-03-2014 06:23:06
09-03-2014 06:28:57
09-03-2014 06:40:39

than later I type this code:

SELECT unix_timestamp('2008-06-19 16:25:15'); <==newtime

I want to collect two query. I want newtime put in unix timestamp query.

Thank you

It looks like you are storing your `date` column as a `VARCHAR` instead of a proper `DATETIME` type. If you are in a position to correct this, you are advised to do so. — Michael Berkowski, Apr 13 '14 at 20:06

score 1 · Answer 1 · answered Apr 13 '14 at 20:36

As Michael Berkowski stated, it would be best to change the type of your date column from varchar to date and that of your time column to time. If you can't do it, you've got to convert your string to datetime. MySQL provides the function STR_TO_DATE() to do this:

SELECT UNIX_TIMESTAMP( 
    STR_TO_DATE( 
        CONCAT_WS(' ', date, time), 
        '%d-%m-%Y %H:%i:%s')
    )
);

check the manual: STR_TO_DATE and DATE_FORMAT for the format string

MYSQL CONCAT_WS and UNIX_TIMESTAMP

1 Answers1