select timestamp from mysql

Question

I have a problem with query where the conditions are related to TIMESTAMP column. So I have a table named table1 which has two columns start_time and end_time. Both of them store TIMESTAMP. I want to query my databse for a particular rows where end_time is higher than given timestamp value and lower than given timestamp value. Basically I want to get all the rows where end_time is between given TIMESTAMP range.

I have found that in my case following query works:

SELECT * FROM table1
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')

But it is not what I want to. I want to give TIMESTAMP value instead of DATE.

What is also strange is that this query returns values that have end_time of earlier date than given timestamp:

SELECT * FROM table1 WHERE end_time > FROM_UNIXTIME( '2015-11-30 20:20:05' )

Query above returns rows where end_timestamp is 2015-11-30 20:18:05

I feel that I am missing something here. Should I use INT instead of TIMESTAMP for my end_time? I believe that the following query should return expected values but it gives me nothing:

SELECT * FROM table1
WHERE end_time >= FROM_UNIXTIME( 1448914740 ) 
AND end_time <= FROM_UNIXTIME( 1448914810 )

Jeffwa · Accepted Answer · 2015-11-15 11:20:39Z

3

You don't need to convert your WHERE clause values using FROM_UNIXTIME. FROM_UNIXTIME converts a TIMESTAMP into a date. If you're sending values to the SQL statement that are already in TIMESTAMP format, your SQL will look like this:

SELECT * FROM table1 WHERE end_time >= 1448914740 AND end_time <= 1448914810

If you are sending date strings (e.g. '2015-11-30 20:14:00' and '2015-11-30 20:14:05') then your SQL would look like this:

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

Since you're storing your values in your table as TIMESTAMP I would recommend you use the first SQL statement.

edited Nov 15, 2015 at 11:20

answered Nov 15, 2015 at 11:11

Jeffwa

1,14310 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

select timestamp from mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related