CODEWITHSUNDEEP
mysqldatetimetimematch
Asim Shahzad
Asim Shahzad

Reputation: 1609

MySQL SELECT WHERE datetime matches time (and not necessarily day)

I have a table which contains a timestamp column. I wish to return all records of a given time regardless of the date.

In other words i want to fetch all records where time is greater then 09:43:00

2015-11-02 09:41:43
2015-11-02 09:43:02
2015-11-02 09:48:27
2015-11-02 09:52:49

Upvotes: 2

Views: 1763

Answers (3)

Nadeem0035
Nadeem0035

Reputation: 3957

SELECT id from table where UNIX_TIMESTAMP(TIME(timestamp_col)) > UNIX_TIMESTAMP(TIME('09:43:00'))

OR 

SELECT id from table where TIME(timestamp_col) > TIME('09:43:00')

OR

SELECT id from table where TIME(timestamp_col) > '09:43:00'

Upvotes: 5

prava
prava

Reputation: 3986

Try this:

SELECT * FROM <table_name>
WHERE date_format(timestamp_col,'%H:%i:%s') > '09:43:00'

Upvotes: 1

Dipanwita Kundu
Dipanwita Kundu

Reputation: 1667

SELECT id,modified_on FROM table
where date_format(modified_on,'%H:%i:%s') > '09:43:30'

Upvotes: 2

Related Questions