homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
unix timestamp in select statement
defanjos




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

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




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

Why doesn't something like the following work?

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

defanjos




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

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

defanjos




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

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




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

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




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

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved