how to append yesterdays data to existing table?

[starbai007]

I simply have a table with Year To Date data. I'm looking to update this table every night at around 9pm with a scheduled task which will run any import function i wish. Using navi-cat i can also add a conditional statement on which to import the data with.

Up until now I have had to manually change the dates within my clause. I believe I am pulling data from a DB2 back end, into a mySQL database locally. Similar to a data-mart business model.

Is there an easy way to do a "CURDATE()-1" where the date/time are merged into one field with the following format:

WHERE CRDATTIM BETWEEN '2008-08-11-00.00.00.000000' AND '2008-08-12-00.00.00.000000'

Thanks for taking the time to read my post.

-Danny

 

[r937]

WHERE CRDATTIM >= CURRENT_DATE - INTERVAL 1 DAY
  AND CRDATTIM  < CURRENT_DATE

r937.com | rudy.ca

 

[starbai007]

Navi loved they syntax, but unfortunately it did not work.

After i put that code into the WHERE condition, and continue with the import it still pulls data from the beginning of the year. I'm assuming that the problem has to do with the way the date/time are together in one field with the insane miliseconds included in the time.

So as of right now i'm still searching but have yet to come to an answer, but I really appreciate your efforts. Thanks for the post Rudy!

 

[r937]

hmm, curious

what is the datatype of the DB2 column?

r937.com | rudy.ca

 

[starbai007]

I believe it is datetime. Is there a way to do CURRENT_DATE:<TIMEFORMAT ie. 00.00.00.000000> - INTERVAL 1 DAY.

 

[starbai007]

Surprised that this forum doesnt have an option to edit a post.

//
> A date is not the same thing as a timestamp in DB2. DATE stores only a
> date (format YYYY-MM-DD). A TIMESTAMP stores date and time (format is
> YYYY-MM-DD-hh.mm.ss.uuuuuu).
\\

So from the looks of it on the DB2 side its stored as a TIMESTAMP, but when i'm importing the table into mySQL it auto sets the field to datetime. It would be nice to be able to keep the format across the board, but if i can not automate a small WHERE statement on an import function for "yesterday's data" than truncating the field would be alright to.

 

[starbai007]

Okay i think i found the issue.

back end has the time down to nanoseconds, but when i import as a timestamp/text/datetime or anything else NaviCat is only able to import up till the seconds. The reason why i need this is because the Create date will be our primary key seeing that no order can come in at that same exact nanosecond.

Any ideas or a work around would be appreciated....

 

[whosrdaddy]

it's generally a bad idea to use a TIMESTAMP as a primary key.

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[starbai007]

I bet it is...but as of right now, this is the only unique identifier we have since the field goes so down to the 6th decimal of a second! I wish there was an easy way to automate this, but I guess no one has come across a solution for this.

 

[starbai007]

AHHA! Basically the back end has the date/time field in a "DBTIMESTAMP" format...which i'm assuming is DB2.

So my question remains how do transition this format over to mySQL using Navicat? I tried every data type supported! A few more days of struggling than I will officially give up