CODEWITHSUNDEEP
phpmysqldatetimestamp
Lenny
Lenny

Reputation: 917

how to select data from mysql using timestamp

This query returns all the rows from my table, even where the end_timestamp is not defined:

SELECT * FROM table 
    WHERE UNIX_TIMESTAMP(end_time) >= UNIX_TIMESTAMP('1448914804') 
    AND UNIX_TIMESTAMP(end_time) <= UNIX_TIMESTAMP('1448914804')

This query works fine but I want to pass timestamp:

SELECT * FROM commercial 
    WHERE UNIX_TIMESTAMP(end_time) >= FROM_UNIXTIME('2015-11-30 20:14:00') 
    AND UNIX_TIMESTAMP(end_time) <= FROM_UNIXTIME('2015-11-30 20:14:05')

Upvotes: 1

Views: 62

Answers (1)

Hasse Bj&#246;rk
Hasse Bj&#246;rk

Reputation: 1601

You are mixing up two types here.

If you created end_time as an INTEGER/LONG, you could do this:

SELECT * FROM table 
    WHERE end_time >= 1448914804  
    AND   end_time <= 1448914804

SELECT * FROM table 
    WHERE end_time >= UNIX_TIMESTAMP('2015-11-30 20:14:00')
    AND   end_time <= UNIX_TIMESTAMP('2015-11-30 20:14:05')

or if it is a DATETIME or TIMESTAMP

SELECT * FROM table 
    WHERE end_time >= FROM_UNIXTIME( 1448914804 ) 
    AND   end_time <= FROM_UNIXTIME( 1448914804 )

SELECT * FROM table 
    WHERE end_time >= '2015-11-30 20:14:00' 
    AND   end_time <= '2015-11-30 20:14:05'

By the way, your first statement refers to table table and the second to commercial.

Upvotes: 2

Related Questions