how to select data from mysql using timestamp

Question

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')

What type do you have for end_time? Is it Date, DateTime, Timestamp or INT/LONG? — Hasse Björk
– Hasse Björk, Commented Nov 14, 2015 at 21:08
Would adding AND end_time IS NOT NULL to the first query fix it? Depends on the default value you set for the column. — lesssugar
– lesssugar, Commented Nov 14, 2015 at 21:09
Hi, end_time is a timestamp. I do have another column in the same table of type timestamp, can that be my problem? — Lenny
– Lenny, Commented Nov 15, 2015 at 9:59

Hasse Björk · Accepted Answer · 2015-11-14 21:46:14Z

2

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.

edited Nov 14, 2015 at 21:46

answered Nov 14, 2015 at 21:21

Hasse Björk

1,61114 silver badges19 bronze badges

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

2 Comments

Lenny Over a year ago

end_time is TIMESTAMP and I'd like too use

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

but for some reasons it returns nothing

Lenny Over a year ago

Your answer is correct. My problem was that I was testing my queries against timestamp from different timezone. I used online converter that showed me timestamp from GMT instead of GMT+1

Collectives™ on Stack Overflow

how to select data from mysql using timestamp

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related