How to specify Date format during LOAD DATA INFILE ?

[Ray in HK]

Will it be possible to specify the date format of type DATE during data
loading ?

[Jonathan]

Re: How to specify Date format during LOAD DATA INFILE ?

Ray in HK wrote:[color=blue]
> Will it be possible to specify the date format of type DATE during data
> loading ?
>
>[/color]
I don't think it is possible. Why not import the data (maybe with the
date column in some new dummy column) and then reformat the data in the
correct representation using a query on the table?

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[Bill Karwin]

Re: How to specify Date format during LOAD DATA INFILE ?

Ray in HK wrote:[color=blue]
> Will it be possible to specify the date format of type DATE during data
> loading ?[/color]

"Although MySQL tries to interpret values in several formats, dates
always must be given in year-month-day order (for example, '98-09-04'),
rather than in the month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98', '04-09-98')."

http://dev.mysql.com/doc/mysql/en/date-and-time-types.html

Regards,
Bill K.

[Ray in HK]

Re: How to specify Date format during LOAD DATA INFILE ?

Thanks, I did read it but just ask if there are any tricks can be played.

"Bill Karwin" <bill@karwin.co m> ¦b¶l¥ó news:d9p8u802fc p@enews1.newsgu y.com
¤¤¼¶¼g...[color=blue]
> Ray in HK wrote:[color=green]
> > Will it be possible to specify the date format of type DATE during data
> > loading ?[/color]
>
> "Although MySQL tries to interpret values in several formats, dates
> always must be given in year-month-day order (for example, '98-09-04'),
> rather than in the month-day-year or day-month-year orders commonly used
> elsewhere (for example, '09-04-98', '04-09-98')."
>
> http://dev.mysql.com/doc/mysql/en/da...ime-types.html
>
> Regards,
> Bill K.[/color]

[Ray in HK]

Re: How to specify Date format during LOAD DATA INFILE ?

Well , it's because there are quite lots of data and for testing purposes, I
need to re-load the data again and again while the dev machine is
s...l..o...w.
Anyway, thx for your advice. It'd be better if Mysql has such feature as
Oracle that allow the format to be specified during batch data loading.

"Jonathan" <jonathan@heela l.nl> ¦b¶l¥ó
news:42c026b5$0 $1589$ba620dc5@ text.nova.plane t.nl ¤¤¼¶¼g...[color=blue]
> Ray in HK wrote:[color=green]
> > Will it be possible to specify the date format of type DATE during data
> > loading ?
> >
> >[/color]
> I don't think it is possible. Why not import the data (maybe with the
> date column in some new dummy column) and then reformat the data in the
> correct representation using a query on the table?
>
> Jonathan
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is it such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?[/color]

[Jonathan]

Re: How to specify Date format during LOAD DATA INFILE ?

> "Jonathan" <jonathan@heela l.nl> ¦b¶l¥ó[color=blue]
> news:42c026b5$0 $1589$ba620dc5@ text.nova.plane t.nl ¤¤¼¶¼g...
>[color=green]
>>Ray in HK wrote:
>>[color=darkred]
>>>Will it be possible to specify the date format of type DATE during data
>>>loading ?
>>>
>>>[/color]
>>
>>I don't think it is possible. Why not import the data (maybe with the
>>date column in some new dummy column) and then reformat the data in the
>>correct representation using a query on the table?
>>
>>Jonathan[/color][/color]

Ray in HK wrote:[color=blue]
> Well , it's because there are quite lots of data and for testing[/color]
purposes, I[color=blue]
> need to re-load the data again and again while the dev machine is
> s...l..o...w.
> Anyway, thx for your advice. It'd be better if Mysql has such feature as
> Oracle that allow the format to be specified during batch data loading.
>[/color]

I still think as it is for testing that you could once do the import and
then afterwards do a conversion as I suggested.

After you have done this backup the mysql table with the mysqldump tool
or the select into method. This way it is possible for you to restore
earlier status just by restoring your tables from the mysql backup/dump
and you only need to do the conversion of the date once.

Normally importing from a text file is slower than inserting from a dump
file as you can use some tricks which are explained here:

Explanation about the mysqldump utility:

http://dev.mysql.com/doc/mysql/en/mysqldump.html

Information about improving the speed of insert statements:

http://dev.mysql.com/doc/mysql/en/insert-speed.html

I cannot imagine that for testing all data need to be reloaded and
converted all the way from the source again as I think you use the same
set for testing over and over again.

Jonathan

N.B. One word of advice for posting in newsgroups/usenet:
I rearanged our conversation a little bit. For the future please don't
top post as this makes reading conversation more difficult. The most
recent answers in newsgroups are normally posted at the bottom and
redundant information is removed. If you want to answer multiple
questions asked in one message just post your answers in between.

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

[nemo]

Re: How to specify Date format during LOAD DATA INFILE ?

[snip][color=blue]
>
>N.B. One word of advice for posting in newsgroups/usenet:
>I rearanged our conversation a little bit. For the future please don't
>top post as this makes reading conversation more difficult. The most
>recent answers in newsgroups are normally posted at the bottom and
>redundant information is removed. If you want to answer multiple
>questions asked in one message just post your answers in between.[/color]
My congratulations on the most sensible, user-friendly advice I've ever read
about top-posting. Too often, all one reads is a snarl "Don't top-post"
with the chastiser then going back to something equally effective and
significant, like - tearing up yesterday's newspaper, opening and closing
the refrigerator door 1,234 times on the night of a crescent moon, and
sticking pins in a wax image of next door's ginger tom. I thought your
approach exemplary. May your tribe increase!

[Ray in HK]

Re: How to specify Date format during LOAD DATA INFILE ?

>[color=blue]
> I still think as it is for testing that you could once do the import and
> then afterwards do a conversion as I suggested.
>
> After you have done this backup the mysql table with the mysqldump tool
> or the select into method. This way it is possible for you to restore
> earlier status just by restoring your tables from the mysql backup/dump
> and you only need to do the conversion of the date once.[/color]

then the testing will be incomplete.

[Jonathan]

Re: How to specify Date format during LOAD DATA INFILE ?

Ray in HK wrote:[color=blue][color=green]
>>I still think as it is for testing that you could once do the import and
>> then afterwards do a conversion as I suggested.
>>
>>After you have done this backup the mysql table with the mysqldump tool
>>or the select into method. This way it is possible for you to restore
>>earlier status just by restoring your tables from the mysql backup/dump
>>and you only need to do the conversion of the date once.[/color]
>
>
> then the testing will be incomplete.
>
>[/color]
Do you really need to test the whole chain over and over again? I think
testing everything modular would improve your development process and
after you've succesfull tested all steps in the sequence you can do some
over all tests of the whole chain.

Or is our goal to port the data to a new database platform regularly?

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?