[susa]

ok, so to recap.

create table:

create table `tbl_name`(`unitid` bigint(10) )

load data into table:

LOAD DATA LOCAL INFILE 'c:\temp\unitid.txt' INTO TABLE tbl_name LINES TERMINATED BY 'rn';

then to match:

select unitid from warehouse

where unitid in (select unitid from tbl_name)

did I get that right?

[susa]

this works

concat(lpad(num1,3,0), num2) as account

[susa]

> unix_timestamp(date_sub(concat(date(now()),' 04:00:00'),interval 7 hour)); — is OK

this does not work, attempting to run (today minus 2 days, ie. March 22, 2008)

so this format works:

between unix_timestamp(date_sub('2008-3-22 00:00:00',interval 7 hour))

and unix_timestamp(date_sub('2008-3-22 23:59:59',interval 7 hour))

this does not work:

between unix_timestamp(date_sub(concat(date(now()-2),' 00:00:00'),interval 7 hour))

and unix_timestamp(date_sub(concat(date(now()-2),' 23:59:59',interval 7 hour))

[susa]

a question

[susa]

>generate the DATETIME string constant dynamically use functions like “concat(date(now()),' 04:00:00')”

so this between unix_timestamp(date_sub('2008-3-12 00:00:00',interval 7 hour))

becomes

between unix_timestamp(date_sub('concat(date(now()),' 04:00:00')',interval 7 hour))

[susa]

peterlaursen wrote:

>You can use a 'notifications service' job.

>But currently we cannot load external files. The query must be in the jobfile itself (between and tags).

assume my query is

SELECT my_codes

FROM table01

where interval_tstamp

between unix_timestamp(date_sub('2008-3-12 00:00:00',interval 7 hour))

and unix_timestamp(date_sub('2008-3-12 23:59:59',interval 7 hour))

what initializes this notifications service job? is it simply me placing a command in task scheduler?

how do I output from here to c:tempoutput.txt ?

[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;

[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

[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]

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' ?

[susa]

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

[Author]

Posts