datetime problem

[Nicky]

Ok, iam having 2 problems with the following lines:

<code>
my $date = strftime('%a %d %b, %I:%M %p', localtime);
Encode::from_to($date, 'ISO-8859-7', 'utf8');
</code>

a) $date in this format wont get inserted into a mysql datetime field
and the cgi script gives me this error: DBD::mysql::st execute failed:
Incorrect datetime value: 'Σαβ 27 Μαϊ, 07:35 μμ' for column
'date' at ...
I must insert it thw way i want although mysql wants the timestamp like
'%y-%m-%d %H:%M:%S' but i dont want to enter ti that way because later
it would need retransfomration.
Is there a way to make perl insert this to the mysql datetime filed
thae way i want it to eb inserted?

b) in order to be able to view corectly my $date with print $date i
must re-encode it to Greek iso other wise i get question marks although
iam using print header( -charset=>'utf8' );

How do you explain this?

 

[Matt Garrish]

Ok, iam having 2 problems with the following lines:

<code>
my $date = strftime('%a %d %b, %I:%M %p', localtime);
Encode::from_to($date, 'ISO-8859-7', 'utf8');
</code>

a) $date in this format wont get inserted into a mysql datetime field
and the cgi script gives me this error: DBD::mysql::st execute failed:
Incorrect datetime value: 'Saß 27 ?a?, 07:35 µµ' for column
'date' at ...
I must insert it thw way i want although mysql wants the timestamp like
'%y-%m-%d %H:%M:%S' but i dont want to enter ti that way because later
it would need retransfomration.
Is there a way to make perl insert this to the mysql datetime filed
thae way i want it to eb inserted?

You have heard the expression "you can't just make shit up and expect it to
work", right?

Don't use a date field if you want to store VARiable CHARacter data (subtle,
eh?). If you really want the benefit of a real timestamp (i.e., for sorting
columns in the table), use two columns.

Matt

 

[Nicky]

Don't use a date field if you want to store VARiable CHARacter data (subtle,
eh?). If you really want the benefit of a real timestamp (i.e., for sorting
columns in the table), use two columns.

That what i as about o ask you now Matt.

Nice idea i will imediately try it but then in another perl cgi script
of mine i try to order by $date some records.
If i store the date as varchar then will i be able to order by date?! I
dont think i could.

 

[Matt Garrish]

That what i as about o ask you now Matt.

Nice idea i will imediately try it but then in another perl cgi script
of mine i try to order by $date some records.
If i store the date as varchar then will i be able to order by date?! I
dont think i could.

Not unless your date perfectly sorts alphabetically. That's why I said to
use two columns. Make one a regular DATE column and insert the proper
numeric date/time manually or using the now() function and store the Greek
equivalent in a separate VARCHAR field. Then you can sort by the table on
the real date column but use the text column to display the Greek text.

Matt

 

[Nicky]

Not unless your date perfectly sorts alphabetically.

Can you xalrify that a bit more?
Because i tried it and tested it and dates are in fatc getting sorted.

 

[Guest]

: Matt Garrish wrote:

: > Not unless your date perfectly sorts alphabetically.

: Can you xalrify that a bit more?
----------^^^^^^^ = clarify?

: Because i tried it and tested it and dates are in fatc getting sorted.

Type slower and it will become faster to read, and faster to get a meaningful
answer.

In your case, you didn't mention whether you sorted a date stored as such
(i.e., with the proper datatype), or a textual representation of it. Dates
should be sorted, that's one of the properties of using a date field; sorting
a pure text field with a textual representation will only sort properly for
a probably very small number of languages.

Oliver.

 

[Gunnar Hjalmarsson]

In your case, you didn't mention whether you sorted a date stored as such
(i.e., with the proper datatype), or a textual representation of it. Dates
should be sorted, that's one of the properties of using a date field; sorting
a pure text field with a textual representation will only sort properly for
a probably very small number of languages.

A date string in accordance with the International Standard ISO 8601 can
easily be sorted. Which languages preclude compliance with ISO 8601?

 

[Guest]

: > a pure text field with a textual representation will only sort properly for
: > a probably very small number of languages.

: A date string in accordance with the International Standard ISO 8601 can
: easily be sorted. Which languages preclude compliance with ISO 8601?

I thought of date strings in textual form, perhaps with names of months
spelt out verbatim (Jan., Feb., etc.). ISO-conformous dates should sort
properly.

Oliver.

 

[Nicky]

I thought of date strings in textual form, perhaps with names of months
spelt out verbatim (Jan., Feb., etc.). ISO-conformous dates should sort
properly.

i use greek string representations for day and month and is getting
sortedf but i still dont know why. iam glad though it does.

here is what i use:

my $date = strftime('%a %d %b, %I:%M %p', localtime);

and iam inserting this to a mysql text filed datatype. and it does get
sorted.

also i dotn understand why in order to view it i have to use:

Encode::from_to($date, 'ISO-8859-7', 'utf8');

 

[Matt Garrish]

i use greek string representations for day and month and is getting
sortedf but i still dont know why. iam glad though it does.

Are you using a ORDER BY statement in your sql, or are you just hoping that
the rows come back out the way they went in? It's never wise to assume,
since there's no guarantee what order you'll get the result set back in.
You'll also lose the ability to select based on a date range if you use text
and not DATE field.

also i dotn understand why in order to view it i have to use:

Encode::from_to($date, 'ISO-8859-7', 'utf8');

That couldn't be any less clear. What is $date? Does it hold the value from
the database or the one going in? What encoding does your database table
use? And what do you mean by view? (command line, browser, something else?)

Matt

 

[Nicky]

Are you using a ORDER BY statement in your sql, or are you just hoping that
the rows come back out the way they went in? It's never wise to assume,
since there's no guarantee what order you'll get the result set back in.
You'll also lose the ability to select based on a date range if you use text
and not DATE field.

of course iam ordering it by date and not just expect it to return the
way i want. But today i had another visit and this didnt ordered
correctly like yesterday

That couldn't be any less clear. What is $date? Does it hold the value from
the database or the one going in? What encoding does your database table
use? And what do you mean by view? (command line, browser, something else?)

iam having this problem when i try to print $date on index.pl not on
inseertiont o database time. why do i have to encoe it to utf8 to view
it ok and it snot by default in utf8 thats myu question.

 

[Matt Garrish]

of course iam ordering it by date and not just expect it to return the
way i want. But today i had another visit and this didnt ordered
correctly like yesterday

That doesn't surprise me. Your result was a fluke, it's just which fluke you
happened to be relying on. If you only have one day's data in your database
then you could very well get them ordered correctly because the only
variation will be on the time, and using a 24-hour clock will make the times
sort alphabetically. Add another day in and now your alphabetic text sort
blows up.

iam having this problem when i try to print $date on index.pl not on
inseertiont o database time. why do i have to encoe it to utf8 to view
it ok and it snot by default in utf8 thats myu question.

That still doesn't mean anything to me. You have to explain what you're
doing, not just write words. If I were to guess, I would say that the string
you enter into the database is in ISO-8859-7 so what you get back is an
ISO-8859-7 string. A utf8 database table should store the string fine, but
there's no implicit conversion to utf8 just because that's what you're
using. So, when you pull the string back out you have an ISO-8859-7 encoded
string not a utf8 one, and when you try to view it as utf8 the character
codes don't match up. Seem plausible to you?

Matt

 

[Nicky]

That doesn't surprise me. Your result was a fluke, it's just which fluke you
happened to be relying on. If you only have one day's data in your database
then you could very well get them ordered correctly because the only
variation will be on the time, and using a 24-hour clock will make the times
sort alphabetically. Add another day in and now your alphabetic text sort
blows up.

Yes! Thats exactly what happened.

That still doesn't mean anything to me. You have to explain what you're
doing, not just write words. If I were to guess, I would say that the string
you enter into the database is in ISO-8859-7 so what you get back is an
ISO-8859-7 string. A utf8 database table should store the string fine, but
there's no implicit conversion to utf8 just because that's what you're
using. So, when you pull the string back out you have an ISO-8859-7 encoded
string not a utf8 one, and when you try to view it as utf8 the character
codes don't match up. Seem plausible to you?

Look! It has nothign to do with the database:
I use:
my $date = strftime('%y-%m-%d %H:%M:%S', localtime);

then if i print $date i get soemthing like ??? 28 ??? 2006, 11:04 ??

but if i also incluide he conversion before printing $date, hwich i do
like this:

my $display_date = strftime('%a %d %b, %I:%M %p', localtime);
Encode::from_to($display_date, 'ISO-8859-7', 'utf8');

then print $adte returns: ÎšÏ…Ï 28 Μαϊ, 11:04 μμ

My question is why on eart i have to re-encode ven the time although
iam using
print header( -charset=>'utf8' );

b) and also why windows just dont save greek filenames in UTF8 as i
tell it to and therefore make me change encoding all the time.

 

[Matt Garrish]

Look! It has nothign to do with the database:
I use:
my $date = strftime('%y-%m-%d %H:%M:%S', localtime);

then if i print $date i get soemthing like ??? 28 ??? 2006, 11:04 ??

but if i also incluide he conversion before printing $date, hwich i do
like this:

my $display_date = strftime('%a %d %b, %I:%M %p', localtime);
Encode::from_to($display_date, 'ISO-8859-7', 'utf8');

then print $adte returns: ??? 28 ?a?, 11:04 µµ

My question is why on eart i have to re-encode ven the time although
iam using
print header( -charset=>'utf8' );

Because what does printing a utf8 header have to do with anything? As I
said, your string is not in utf8. If you're getting it from your system's
strftime, then your system must be using ISO-8859-7. There is no implicit
conversion to utf8 just because you print an html header. All that does is
tell the browser what to expect. You still have to send it utf8 data, which
your $date obviously is not.

Matt

 

[Nicky]

Because what does printing a utf8 header have to do with anything? As I
said, your string is not in utf8. If you're getting it from your system's
strftime, then your system must be using ISO-8859-7. There is no implicit
conversion to utf8 just because you print an html header. All that does is
tell the browser what to expect. You still have to send it utf8 data, which
your $date obviously is not.

I see. So i had a false sence that if i print a HTTP and HTML header of
'UTF8' then all text that i will create/use in my cgi script will be of
UTF8 nature or will be converted internally to that. Now i see and
thank you for clearing this out to me.

Also please can you tell me why my system is gettign datetime in Greek
Iso and not utf8 although iam usign English XP?

and the last question that remaines unanswered although i ahve asked it
in many irc channels and forum places is why the greek file name string
arent UTF8 as i saved them when creating the file.
It it were then it would have benn appeared correctly in the popup menu
and they wouldnt require 3 re-encodingsa back and forth from utf8 =>
iso-8859-7 and so on.
here is the code again:

my @files = <../data/text/*.txt>;
my @display_files = map /([^\/]+)\.txt/, @files;
Encode::from_to($_, "ISO-8859-7", "utf8") for @display_files; #1st
conversion in order to appear correctly in the popup menu

print br;
print start_form( action=>'index.pl' );
print h1( {class=>'lime'}, "Επέλεξε το κείμενο
που σε ενδιαφέÏει => ",
popup_menu( -name=>'select',
-values=>\@display_files ),
submit('Εμφάνιση'));
print end_form;

my $passage = param('select') || "ΑÏχική Σελίδα!";
Encode::from_to($passage, "utf8", "ISO-8859-7") if param();
#2nd conversion in order for the user selected file from the popup menu
to be able to be opened

if ( param('select') )
{
open(FILE, "<../data/text/$passage.txt") or die $!;
local $/;
$data = <FILE>;
close(FILE);

Encode::from_to($passage, "ISO-8859-7", "utf8");
#3nd conversion in order for the user selected file from the popup menu
to be able to be inserted as "UTF8" in the database.

$select = $dbh->prepare( "UPDATE guestlog SET passage=?, date=?,
counter=counter+1 WHERE host=?" );
$select->execute( $passage, $date, $host );
}
else

 

[Nicky]

Because what does printing a utf8 header have to do with anything? As I
said, your string is not in utf8. If you're getting it from your system's
strftime, then your system must be using ISO-8859-7. There is no implicit
conversion to utf8 just because you print an html header. All that does is
tell the browser what to expect. You still have to send it utf8 data, which
your $date obviously is not.

I see. So i had a false sence that if i print a HTTP and HTML header of
'UTF8' then all text that i will create/use in my cgi script will be of
UTF8 nature or will be converted internally to that. Now i see and
thank you for clearing this out to me.

Also please can you tell me why my system is gettign datetime in Greek
Iso and not utf8 although iam usign English XP?

and the last question that remaines unanswered although i ahve asked it
in many irc channels and forum places is why the greek file name string
arent UTF8 as i saved them when creating the file.
It it were then it would have benn appeared correctly in the popup menu
and they wouldnt require 3 re-encodingsa back and forth from utf8 =>
iso-8859-7 and so on.
here is the code again:

my @files = <../data/text/*.txt>;
my @display_files = map /([^\/]+)\.txt/, @files;
Encode::from_to($_, "ISO-8859-7", "utf8") for @display_files; #1st
conversion in order to appear correctly in the popup menu

print br;
print start_form( action=>'index.pl' );
print h1( {class=>'lime'}, "Επέλεξε το κείμενο
που σε ενδιαφέÏει => ",
popup_menu( -name=>'select',
-values=>\@display_files ),
submit('Εμφάνιση'));
print end_form;

my $passage = param('select') || "ΑÏχική Σελίδα!";
Encode::from_to($passage, "utf8", "ISO-8859-7") if param();
#2nd conversion in order for the user selected file from the popup menu
to be able to be opened

if ( param('select') )
{
open(FILE, "<../data/text/$passage.txt") or die $!;
local $/;
$data = <FILE>;
close(FILE);

Encode::from_to($passage, "ISO-8859-7", "utf8");
#3nd conversion in order for the user selected file from the popup menu
to be able to be inserted as "UTF8" in the database.

$select = $dbh->prepare( "UPDATE guestlog SET passage=?, date=?,
counter=counter+1 WHERE host=?" );
$select->execute( $passage, $date, $host );
}
else more code

As you can see this re-encoding stuff its becaming very tredious and
not only to this script(index.pl) but in other as well.

 

[Dr.Ruud]

Nicky schreef:

Encode::from_to($date, 'ISO-8859-7', 'utf8');

a) $date in this format wont get inserted into a mysql datetime field

You shouldn't store (string) representations of dates, you should store
them with the date-type of the particular database (or rather the
datetime-type since often the timezone is important too).

The conversion to string is what you do just before you need to show it,
so before you put the date on screen or in print.

 

[Matt Garrish]

I see. So i had a false sence that if i print a HTTP and HTML header of
'UTF8' then all text that i will create/use in my cgi script will be of
UTF8 nature or will be converted internally to that. Now i see and
thank you for clearing this out to me.

Also please can you tell me why my system is gettign datetime in Greek
Iso and not utf8 although iam usign English XP?

I'm assuming that you're using the POSIX module to get strftime
functionality, correct? If so, then you must have your regional settings set
to Greek in order to get that language back. Ipso facto, the Greek regional
settings in Windows XP must use ISO-8859-7. Don't ask me the why's of
Windows, though. You might catch Bill down the hall.

Matt

 

[Nicky]

I'm assuming that you're using the POSIX module to get strftime
functionality, correct? If so, then you must have your regional settings set
to Greek in order to get that language back. Ipso facto, the Greek regional
settings in Windows XP must use ISO-8859-7. Don't ask me the why's of
Windows, though. You might catch Bill down the hall.

Yes i was using POSIX module.

So about the greek string file names does anyone else has a clue why by
default they arent saved in UTF8 and ui have to do this tedious
conversions each time?

 

[Dr.Ruud]

Nicky schreef:

So about the greek string file names does anyone else has a clue why
by default they arent saved in UTF8

You shouldn't care about how a filesystem saves things, you should find
out how to set and how to get the filenames, in the proper encoding, or
just refrain from special characters.

Please run this and reply with the output:

perl -MWin32 -e "printf qq{%s\t%08x\t%s\n}, Win32::FsType"

On a W2K-system here, it prints
NTFS 000700ff 255