OK, thank you. How is the speed of this index compared with an indexed
date column if I do:
year_number='x' and month_number='y' and day_number='z';
They should have about the same cardinality, right?
Thanks,
Anders
Chris wrote:
Anders Lundgren wrote:
One potential solution might be to
Hi Olaf, all !
Olaf Stein wrote:
Hi All
If I have a column
`consent` enum('Y','N','P') default NULL,
And I try to insert 'NULL' I get this error:
Warning: Data truncated for column 'consent' at row 1
What is the problem there?
Double-check your command:
'NULL' is a string of four (4)
Hi,
I am trying to deal with empty values so that I can graph data over a
24 hour period without gaps.
I created a table called HOURS which simply has 0-23 and I am trying
to do a join on this to produce the desired results. I think that the
DATE_FORMAT in the query is screwing things up.
The
Splitting out your values will cause problems where doing greater than/less
than searching.
If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return
really only the second half of each year from 2000 on. To include 2/2002,
You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem
isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT.
I would think you would be getting
That query doesn't return empty values. Just to clarify what I want as
the result:
My initial query was this,
mysql select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event
WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11
04:00:00' AND sid=1 GROUP BY hour;
+--+--+
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be
Ugh. My perl isn't quite right there. Here's a bit better (e.g.
working) example:
If you create the table, then put this in populate_hours.pl:
BEGIN
#!/usr/bin/perl
$counter = 0;
while ($counter 100) {
print INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD('2000-01-01 00:00:00',
Try something like this:
SELECT
SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour
FROM HOUR
LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM
event) as e on HOURS.hour =e.hr
WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11
04:00:00' AND sid=1
I did a DB conversion before that with ALTER DATABASE db_name
CHARACTER SET utf8
That worked wonderfully, except not as expected. ;-)
It basically converted only the database itself. so I had to do a
separate ALTER TABLE ... for each table.
The database encoding more establishes the default
Oh, sorry. I set up a test table and then to send the query to the list,
I changed the table names and column names to match yours...but I missed
some. I think this one will work.
SELECT
SUM(CASE when e.sid is null then 0 else 1 end) as counts, HOURS.hour
FROM HOURS
LEFT OUTER JOIN (SELECT
Dear MySQL users,
MySQL Community Server 5.0.33, a new version of the popular Open Source
Database Management System, has been released. The release is now
available in source form from our download pages at
http://dev.mysql.com/downloads/
and mirror sites.
Note that not all mirror sites
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE
o.ID=a.ID;
If update does not support aliases, is there another way to do this query? I
am usin V3.23.54. Any help would be
Yes, of course. Thank you!
- Anders
Brent Baisley wrote:
Splitting out your values will cause problems where doing greater
than/less than searching.
If you search on year_number=2000 and month_number=6, that's not going
to give you everything from 6/2000 on. It will return really only the
Richard Reina wrote:
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID;
First off, it'd be best if possible (I know some cases prevent it) to
upgrade your server. The
Hi Richard,
Richard Reina wrote:
I am trying to update from one table to another but I get a syntax error when I
try:
UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID;
If update does not support aliases, is there another way to do this query? I am usin
Reading the noted previous thread, I was curious as to updating multiple
tables. I read the MySQL docs, which mentions that you can do it:
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] /|table_references|/
SET /|col_name1|/=/|expr1|/ [, /|col_name2|/=/|expr2|/ ...]
[WHERE
Columns can have character set definitions, also. In this case, I hope not.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Chris White [mailto:[EMAIL PROTECTED]
Sent:
The proper syntax would need to be:
UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE
o.ID=a.ID;
The only problem is the existence of the from.
That being said, an UPDATE ... JOIN likely doesn't work under MySQL 3
-Original Message-
From: Chris White [mailto:[EMAIL
Hello the list
I have a bunch of data that I load in the base through
the load data infile procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert day/month/year_4digit into
Dear friends,
Please help me to understand several LOCKing the same tables without
unlocking them between LOCKs. Imagine the following code:
LOCK TABLES t1 READ, t2 READ;
-- some hard select queries which need that other threads do not update tables
LOCK TABLES t1 WRITE, t2 WRITE;
--
[EMAIL PROTECTED] (Paul Halliday) wrote in
news:[EMAIL PROTECTED]:
I am trying to deal with empty values so that I can graph data over a
24 hour period without gaps.
Have a look here:
http://forums.mysql.com/read.php?10,133603,133607#msg-133607
--
felix
--
MySQL General Mailing List
For
Hi all,
Multi-table updates are not possible for versions older than 4.0.4.
(http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is
not possible with your current version.
To be complete, though, each of you missed the second syntax error in
his statement
Jonathan
The manual indicates that you can specify a specific level to control
what types of warnings are logged:
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
(See section on log-warnings.)
But all I really get from this reading is 0 turns it off, 1 prints some
warnings, and 2 prints level
Yes, the two examples are equivalent.
UNLOCK TABLES releases any locks held by the current thread. All tables
that are locked by the current thread are implicitly unlocked when the
thread issues another LOCK TABLES...
So there is a hole there with either example. In order to keep others from
Hi,
STR_TO_DATE() simply converts the given format string to datetime value. So
to change the format of the date dispaly, go for DATE_FORMAT().
For Instance,
mysql select DATE_FORMAT('2007/10/01','%d/%m/%Y');
or
mysql select DATE_FORMAT(datecolumn,'%d/%m/%Y') from table1;
Thanks
ViSolve DB
Hi Reina,
Try like:
mysql UPDATE maindb o,altdb ao set o.price =ao.price where o.id=ao.id;
This will do good.
Thanks
ViSolve DB Team
- Original Message -
From: Richard Reina [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, January 10, 2007 10:08 PM
Subject: Does
27 matches
Mail list logo
