Welcome to WebmasterWorld Guest from 54.196.238.210

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

unix timestamp in select statement

     

defanjos

4:09 pm on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I need a mysql select statement to get all records in which a unix timestamp field in the db (mydate) matches today's date.

i.e.
select * FROM tbl123 WHERE mydate = date('Y-m-d')

I know i can get the date in the php code like, gmdate("Y-m-d", $mydate), but how can I do it within the sql statement.

thanks in advance

defanjos

5:19 pm on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Why doesn't something like the following work?

SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = DATE(CURDATE())

defanjos

5:22 pm on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



or:
SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE()

defanjos

6:22 pm on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



This seems to work:

SELECT * FROM tbl123 WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) = DATE(from_unixtime(mydate,'%Y-%m-%d'))

not sure if it chooses just records that match curdate(), or any date going back 24 hours from the time the query is run.

I need records just for that day, for example if curdate() is 2014-01-25, i need all records from 2014-01-25 in the database.

penders

5:49 pm on Jan 26, 2014 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = DATE(CURDATE()) 
SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE()


Your first two examples appear to work OK for me. And likewise, so does the following (I think the format string is unnecessary in this case):
SELECT * FROM tbl123 WHERE DATE(FROM_UNIXTIME(mydate)) = CURDATE()


Your last example (which you say works for you) does not work for me, in fact I can't see how this can work. DATE_SUB(CURDATE(),INTERVAL 1 DAY) returns yesterdays date.

Hhmmm, I wonder... is your DB server running in a different timezone? MySQL date/time functions are locale aware, whereas a unix timestamp does not contain any timezone information and usually (should) refer to UTC time.

defanjos

6:33 pm on Jan 29, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Thanks penders, the problem was the timezone in the server was not the same as the CRM (where I was pulling the data from)

Now, SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE(), works fine.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month