How To Select Time_stamp = Today ?

[susa]

brand new user to SQLyog

how do I do this query and show dtime like 06/20/07 10:10:20 ?

SELECT

name

, FORMAT (time_stamp, “mm/dd/yy:hh:mm:ss” ) as dtime

FROM

mydb.tbl01

WHERE (color > 3

AND time_stamp = '2007/06/20')

limit 0, 50;

this is just a mockup as I don't know the correct format to use or syntax

time_stamp has value like 1150378193

[peterlaursen]

the mysql docs explains how to format.  Look in the paragraph on “DATE and TIME functions”

[susa]

peter, thank you for the reply – you couldn't possibly show me the syntax based on my example, could you?

[peterlaursen]

I think you should devote a couple of hours to study

http://dev.mysql.com/doc/refman/5.0/en/dat…-functions.html

http://dev.mysql.com/doc/refman/5.0/en/dat…tion_get-format

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

-> 'Saturday October 1997'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

-> '22:23:00'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

'%D %y %a %d %m %b %j');

-> '4th 97 Sat 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

'%H %k %I %r %T %S %w');

-> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

-> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

-> '00'

[susa]

peter, thank you for the examples

time_stamp has value like 1150378193 so does

SELECT DATE_FORMAT(time_stamp, '%MM %DD %YY')

give me '06 21 07' ?

[peterlaursen]

try for yourself from SQLyog SQLpane (and read the docs again and again!).

You won't have to store any data to test this!

[susa]

I tried and the following are my results

If I use select time_stamp

I get 1150378193

If I use select DATE_FORMAT(time_stamp, '%M %D %Y')

I get (null)

Not sure what I did wrong and yes, I read carefully all the examples, still unable to get my original question answer

please, if you know what I am doing wrong, help me.

[susa]

If I do select DATE_FORMAT(FROM_UNIXTIME(time_stamp),'%M%D%Y')

I get June15th2006

that is close but I would like to see 061506

ok, it's select FROM_UNIXTIME(time_stamp,'%m%d%y')

I would love to be able to do

select …. where time_stamp = now()-2

but I suspect time_stamp would have to contain some kind of modifier to evaluate to now()-2

[peterlaursen]

“but I would like to see 061506”

If there is no direct way you will have to generate day, mont and year seperately and concat() them (or concat(trim())

“select …. where time_stamp = now()-2”

i think you can but I think it will only subtract 2 seconds.  If you want to subtract 2 hours or days I think you must give teh # seconds.

I think everybody reads the doc and experiments a little with this when they need!

[susa]

> If there is no direct way

there is a direct way, it's as I posted simply

select FROM_UNIXTIME(time_stamp,'%m%d%y')

which returns 061506

now, since that works, to get today minus 2 days, it's simply

FROM_UNIXTIME(time_stamp,'%m%d%y') = DATE_FORMAT(DATE_SUB(now(),INTERVAL 2 DAY),'%m%d%y')

so, for those that many need help in the future, the correct syntax is

SELECT

name

, FROM_UNIXTIME(time_stamp,'%m%d%y') as dtime

FROM

mydb.tbl01

WHERE (color > 3

AND FROM_UNIXTIME(time_stamp,'%m%d%y') = '062007'

limit 0, 50;

if one wanted today minus 2 days, it's simply

SELECT

name

, FROM_UNIXTIME(time_stamp,'%m%d%y') as dtime

FROM

mydb.tbl01

WHERE (color > 3

AND FROM_UNIXTIME(time_stamp,'%m%d%y') = DATE_FORMAT(DATE_SUB(now(),INTERVAL 2 DAY),'%m%d%y')

limit 0, 50;

[peterlaursen]

yes .. use INTERVAL of course 🙂

[Author]

Posts