MySQL 8.x GA release date?

[Tim Holme]

Is there a planned release date for MySQL 8.x generally available release?

Re: mysql query for current date accounting returns NULL

[shawn l.green]

On 3/26/2016 4:36 PM, shawn l.green wrote:



On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:

I have Freeradius 2.x with MySQL 5.5 in Ubuntu.

I want to query user quota for current date. I am using following code

SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM
radacct where (acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d')
AND NOW() AND acctstoptime  between  DATE_FORMAT(NOW() ,'%Y-%m-%d')
AND NOW()) AND radacct.username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user
have not disconnected yet (and have no previous session for today) it
returns NULL.

So how can i can get the value even if user acttstoptime is null?




Try this...(using an earlier suggestion to the thread)

SELECT
  SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
   radacct.username='%{User-Name}'
   AND acctstarttime BETWEEN CURDATE() AND NOW()
   AND (
 acctstoptime  <= NOW()
 OR acctstoptime IS NULL
   )

But in reality, can you have an acctstarttime that is >= NOW()? If not,
then you can also simplify that term to just



oops! one too many AND's

   AND AND acctstarttime >= CURDATE()


I meant to write
AND acctstarttime >= CURDATE()



and lose the BETWEEN comparison.



--
Shawn

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysql query for current date accounting returns NULL

[shawn l.green]

On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:

I have Freeradius 2.x with MySQL 5.5 in Ubuntu.

I want to query user quota for current date. I am using following code

SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where 
(acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND 
acctstoptime  between  DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND 
radacct.username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user have not 
disconnected yet (and have no previous session for today) it returns NULL.

So how can i can get the value even if user acttstoptime is null?




Try this...(using an earlier suggestion to the thread)

SELECT
 SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
  radacct.username='%{User-Name}'
  AND acctstarttime BETWEEN CURDATE() AND NOW()
  AND (
acctstoptime  <= NOW()
OR acctstoptime IS NULL
  )

But in reality, can you have an acctstarttime that is >= NOW()? If not, 
then you can also simplify that term to just


  AND AND acctstarttime >= CURDATE()

and lose the BETWEEN comparison.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: mysql query for current date accounting returns NULL

[Hal.sz S.ndor]

2016/03/25 06:39 ... JAHANZAIB SYED:

I want to query user quota for current date. I am using following code

SELECT SUM(acctinputoctets)+SUM(acctoutputoctets) AS Total FROM radacct where 
(acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND 
acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND 
username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user have not 
disconnected yet (and have no previous session for today) it returns NULL.


That expression has problems. Not only it works only when both 
acctstarttime and acctstoptime are good, but only if they are on the 
same day, today.


> So how can i can get the value even if user acctstoptime is null?
Really, it is best to omit the test on "acctstoptime".

I don't like the form of the test, either. If "acctstarttime" is of 
DATETIME (or TIMESTAMP) type I like this better:

acctstarttime BETWEEN CURDATE() AND NOW()
otherwise
CAST(acctstarttime AS DATETIME) BETWEEN CURDATE() AND NOW()

You are also not GROUPing BY anything, which, strictly speakind, with 
SUM is bad SQL, but, of course, it works because only one value of 
"username" is sought.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

mysql query for current date accounting returns NULL

[JAHANZAIB SYED]

I have Freeradius 2.x with MySQL 5.5 in Ubuntu. 

I want to query user quota for current date. I am using following code

SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where 
(acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND 
acctstoptime  between  DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND 
radacct.username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user have not 
disconnected yet (and have no previous session for today) it returns NULL.

So how can i can get the value even if user acttstoptime is null?

  

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Roberta Jask�lski]

 2014/11/04 11:04 -0800, Jan Steinman 
I was not suggesting it for dates. The OP appeared to have a well-defined set 
of strings in a VARCHAR field — THAT is what I suggested ENUMs for!



What is the update frequency of those VARCHARs? If you're adding them often — 
or if you need to occasionally change their value — I'd use another table with 
a reference.

If they're immutable and new ones are not added often, there's no design cost 
at all to using ENUMs. I'd argue there's a higher maintenance cost to NOT using 
them! 

Ugh--I missed the discussion shift from the DATEs to the VARCHAR labels ... and 
now I wholeheartedly agree with you.

As for the DATEs, I yet suspect that for performance maybe TIMESTAMP is 
slightly better than DATE.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

2014-11-06 21:49 GMT+01:00, Roberta Jaskólski h...@tbbs.net:

 Ugh--I missed the discussion shift from the DATEs to the VARCHAR labels ...
 and now I wholeheartedly agree with you.

 As for the DATEs, I yet suspect that for performance maybe TIMESTAMP is
 slightly better than DATE.

Well what I'm interested in - and I was asking my original question
about - is SIGNIFICANT difference. If everything I can count for is
just slight improvements (by which I understand difference that can
be detected only by benchmarks, but not really during database
operation), then actually it doesn't make much sense, it seems.
-- 
Zbig

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Johan De Meersman]

- Original Message -
 From: Zbigniew zbigniew2...@gmail.com
 Subject: Using INTEGER instead of VARCHAR/DATE - is this a way to faster 
 access?

 What about using ENUMs? They have nearly the performance of INTEGERs, but
 you don't have to maintain a string mapping in your programming logic.
 
 But are you able to estimate, what boost can i notice? 5% - or 50%,
 or maybe even 500%?

I'll give you an interesting reason to switch to ENUM (or smallint, if so 
inclined): Your data fields will be smaller. That not only means more records 
in a page (might be negligable), but more importantly, it'll make the index on 
that field smaller, meaning a) more of it will remain in memory and b) lookups 
on it will be marginally faster, too.

I have no hard data on how it'll impact index performance (your dataset is 
yours to benchmark), but on one million of records (and you were talking 
several), a each byte saved is a megabyte of memory that can be used for other 
purposes, like data cache, which will speed up other things, too.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: (Halász Sándor) h...@tbbs.net
 
 2014/10/29 20:56 +0100, Zbigniew 
 if instead of textual labels I'll use SMALLINT as simple integer code for 
 each different label (and in my program a translation table)? 
 
 This, in effect, is ENUM...

Well, not really! With INTEGERs, your referential integrity is external to 
MySQL, and has to be managed.

 ... and maybe is useful if the set of dates is well determined...

I was not suggesting it for dates. The OP appeared to have a well-defined set 
of strings in a VARCHAR field — THAT is what I suggested ENUMs for!

 There is a design cost in using ENUM: If you find that your first set of 
 dates is too small, later, with ALTER TABLE, you have to change the type.

Again, the suggestion for ENUM was to replace a constrained set of VARCHARs, 
and yet, you raise a valid point.

What is the update frequency of those VARCHARs? If you're adding them often — 
or if you need to occasionally change their value — I'd use another table with 
a reference.

If they're immutable and new ones are not added often, there's no design cost 
at all to using ENUMs. I'd argue there's a higher maintenance cost to NOT using 
them!

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Hal�sz S�ndor]

 2014/11/02 13:19 +0100, Zbigniew 
So you guys (Jan and hsv) suggest, that switching from DATE to more
numeric data type may not be necessary, but using ENUM instead of
VARCHAR can be real performance gain, right?

But are you able to estimate, what boost can i notice? 5% - or 50%,
or maybe even 500%? 

_Maybe_! but I will not venture to measure it. I doubt that it would be big. I 
just looked ENUM up: it allows 65535 distinct values, which sounds like 16 
bits, usually two bytes, and numeric operators would be used.

 2014/11/02 11:19 -0800, Jan Steinman 
I would hope that the query optimizer converts '2014-11-02' to the three-bytes 
internal representation of DATE before doing the query, in which case, DATE 
should actually be a tiny bit faster than TIMESTAMP. 

That is doubtful. In the processors that I know, one built-in numeric operation 
is enough for either 2 bytes of ENUM or 4 bytes of TIMESTAMP, but three are 
needed for DATE. In any case, the C-overhead, since MySQL is implemented in C, 
overwhelms the cost of any single comparison. The equality comparison is at 
least simpler than an ordered comparison.

 2014/10/29 20:56 +0100, Zbigniew 
if instead of textual labels I'll use SMALLINT as simple integer code for 
each different label (and in my program a translation table)? 

This, in effect, is ENUM, and maybe is useful if the set of dates is well 
determined. If you use ENUM at least the overhead of translation is built in 
into MySQL, and, one hopes, costs less than doing it for oneself.

There is a design cost in using ENUM: If you find that your first set of dates 
is too small, later, with ALTER TABLE, you have to change the type. If you add 
the new string to the end, there is, maybe, no great cost to the adding, but if 
in the middle If every new date-string is added to the end, it will, maybe, 
be in random order. The same applys to your translation table.

I suggested TIMESTAMP because I suspect that one built-in comparison (after the 
optimizer is done with it) is enough, and it allows the appearance of real 
dates. (If the processor fetches 32 bits at a time (nowadays 64 or more is 
likly) then a 32-bit type is fetched as swiftly as any other. Both shorter and 
longer types take longer.) The more I debate this, the better I like TIMESTAMP 
for your problem.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

2014-10-31 5:29 GMT+01:00, Jan Steinman j...@ecoreality.org:

 What about using ENUMs? They have nearly the performance of INTEGERs, but
 you don't have to maintain a string mapping in your programming logic.

So you guys (Jan and hsv) suggest, that switching from DATE to more
numeric data type may not be necessary, but using ENUM instead of
VARCHAR can be real performance gain, right?

But are you able to estimate, what boost can i notice? 5% - or 50%,
or maybe even 500%?
-- 
Zbig

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 switching from DATE to more numeric data type may not be necessary...

I would hope that the query optimizer converts '2014-11-02' to the three-bytes 
internal representation of DATE before doing the query, in which case, DATE 
should actually be a tiny bit faster than TIMESTAMP.

 using ENUM instead of VARCHAR can be real performance gain, right?

Not just in performance, but it appears to simply be The Right Thing To Do(TM) 
in your case. (Codd Rule #10: referential integrity.)

Consider an insert into a day-of-week column (for instance) that somehow got 
Sudnay in the VARCHAR field instead of Sunday. Using an ENUM eliminates the 
possibility of a typo at a more fundamental level than your programming logic. 
If you do a massive insert with Sudnay in the ENUM field, the entire 
transaction will fail, which is really what you want rather than having to 
track down bad data after the fact, no?

If it REALLY is one value out of a known set, it SHOULD be either an ENUM, or a 
reference to another table. Use the latter technique if you need to add new 
values very often.

 But are you able to estimate, what boost can i notice? 5% - or 50%, or 
 maybe even 500%?

Very hard to say. That's like saying, If I eat well and get enough exercise, 
will I live 5% or 50% or 500% longer? Probably more like 5%, but it may FEEL 
like 500%! :-)

If the value is constrained to a set, having it as an ENUM (or reference to 
another table) will save you grief in many other ways besides boosting 
performance.

 Private enterprise, indeed, became too private. It became privileged 
enterprise, not private enterprise. -- Franklin Delano Roosevelt
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[hsv]

 2014/10/29 20:56 +0100, Zbigniew 
Now to the point: considering, that the second column shall contain
about 100-200 different labels - so in average many of such labels
can be repeated one million times (or even more) - will it speed-up
the selection done with something like ...WHERE label='xyz' AND
date='2013-02-25'  (and maybe the insertion as well?), if instead of
textual labels I'll use SMALLINT as simple integer code for each
different label (and in my program a translation table)?

If so - will I have further performance gain, if instead of
human-readable DATE for the third column I'll use Unix time put into
INTEGER type column, not DATE-type? 

I really cannot answer your real question, but say only that DATE and other 
time types are numeric, although mostly constructed.
DATE takes three bytes with range '1000-01-01' to '-12-31'.
TIMESTAMP (which has special features that one can suppress) is a four-byte 
integer that is a 31-bit Unix timestamp with range '1970-01-01 00:00:01.00' 
UTC to '2038-01-19 03:14:07.99' UTC.

Maybe TIMESTAMP, which doubtless uses the underlying integer mechanism for 
comparison, is best for you. Consider also the functions UNIX_TIMESTAMP and 
FROM_UNIXTIME.

The zone is not involved in DATE, but is involved in the rest aforesaid.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs?

They have essentially the performance of INTEGERs, but you don't have to 
maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs? They have nearly the performance of INTEGERs, but you 
don't have to maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

I'm going to establish a large database (ISAM) of simple structure,
say the table with only 3 columns:

- VARCHAR(80)
- VARCHAR(40)
- DATE

The number of rows can be quite large, about hundred million or so.
The first column, containing actual information, will contain unique
values, unlike the two others - but the two others shall be used for
data selection (and I'll index them).

Now to the point: considering, that the second column shall contain
about 100-200 different labels - so in average many of such labels
can be repeated one million times (or even more) - will it speed-up
the selection done with something like ...WHERE label='xyz' AND
date='2013-02-25'  (and maybe the insertion as well?), if instead of
textual labels I'll use SMALLINT as simple integer code for each
different label (and in my program a translation table)?

If so - will I have further performance gain, if instead of
human-readable DATE for the third column I'll use Unix time put into
INTEGER type column, not DATE-type?

And the final question: even, if so - is it worthy? I mean: will the
supposed performance gain be significant (e.g. 2-3 times faster
selection) - and not, say, just 5% faster (only possible to detect by
using benchmarking tools)?

Thanks in advance for your opinions.
-- 
Zbig

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: converting numeric to date-time?

[hsv]

 2014/09/04 08:40 -0700, Jan Steinman 
 From: Ed Mierzwa (emierzwa) emier...@micron.com
 
 
 FROM_UNIXTIME(1409304102.153) /*your epoch column here*/

I don't think the OP has a Unix timestamp.

The number looks suspeciously like concatenation of date digits, 140930 at 
the beginning looks like September 30, 2014.

If that's the case, you need to write something that will tear it apart. 

MySQL s interpretation of timestamps is already such that not much such code is 
needed: see Overview of Date and Time Types. If this, 140930, really were 
September 30, 2014 it would be enough to write
SELECT DATE(140930)
This also works:
SELECT CAST(140930210215 AS DATETIME)
One does not need to write apart-tearing code.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: converting numeric to date-time?

[Glyn Astill]

 From: Jan Steinman j...@ecoreality.org
To: mysql@lists.mysql.com 
Sent: Thursday, 4 September 2014, 16:40
Subject: RE: converting numeric to date-time?
 

 From: Ed Mierzwa (emierzwa) emier...@micron.com
 
 
 FROM_UNIXTIME(1409304102.153)/*your epoch column here*/

I don't think the OP has a Unix timestamp.


Really? Looks like a unix epoch to me.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: converting numeric to date-time?

[Johan De Meersman]

- Original Message -
 From: Jan Steinman j...@ecoreality.org
 Subject: RE: converting numeric to date-time?
 
 I don't think the OP has a Unix timestamp.

OP explicitly says epoch including milliseconds - so it's going to be three 
digits too long :-)

divide by 1000; split off decimal; from_unixtime(epoch) and add a millisecond 
display.

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: converting numeric to date-time?

[Jan Steinman]

 From: Ed Mierzwa (emierzwa) emier...@micron.com
 
 
 FROM_UNIXTIME(1409304102.153) /*your epoch column here*/

I don't think the OP has a Unix timestamp.

The number looks suspeciously like concatenation of date digits, 140930 at 
the beginning looks like September 30, 2014.

If that's the case, you need to write something that will tear it apart.

 Nobody talks more of free enterprise and competition and of the best man 
winning than the man who inherited his father's store or farm. -- C. Wright 
Mills
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: converting numeric to date-time?

[Ed Mierzwa (emierzwa)]

SET @tz=@@session.time_zone ;
SET SESSION time_zone = '+0:00' ;
SELECT DATE_FORMAT(
 FROM_UNIXTIME(1409304102.153)  /*your epoch column here*/
 ,'%Y-%m-%d %a %H:%i:%s.%f GMT');
SET SESSION time_zone = @tz ;

 2014-08-29 Fri 09:21:42.153000 GMT


 (or)


SELECT DATE_FORMAT(
 FROM_UNIXTIME(1409304102.153) - INTERVAL ( 
TIMESTAMPDIFF(SECOND,UTC_TIMESTAMP(),NOW()) ) SECOND
 ,'%Y-%m-%d %a %H:%i:%s.%f GMT') ;

 2014-08-29 Fri 09:21:42.153000 GMT


-Original Message-
From: Philip Amadeo Saeli [mailto:psa...@zorodyne.com] 
Sent: Monday, September 01, 2014 5:51 PM
To: Rajeev Prasad
Cc: MYSQL General List
Subject: Re: converting numeric to date-time?

* Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]:
 I have a column in a table which is epoch time including milliseconds.
 
 e.g. = 1409304102153 
 
 
 now i want to display all fields in the table but this field as: 2014-8-29 
 Fri 09:21:42: GMT  (whatever comes in )
 
 
 and i am not finding anything on web about how to do that.
 
 can anyone help please.
 
 ty.
 Rajeev

I do not know how to do it directly in MySQL, but if you can dump the
table and post-process, this may be helpful on Linux:  The date(1) cmd
can translate between formats, e.g. (taking the above value),

  date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N
  2014-08-29 Fri 04:21:42.15300

--Phil

-- 
Philip Amadeo Saeli
openSUSE, CentOS, RHEL
psa...@zorodyne.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

converting numeric to date-time?

[Rajeev Prasad]

I have a column in a table which is epoch time including milliseconds.

e.g. = 1409304102153 


now i want to display all fields in the table but this field as: 2014-8-29 Fri 
09:21:42: GMT  (whatever comes in )


and i am not finding anything on web about how to do that.

can anyone help please.

ty.
Rajeev

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: converting numeric to date-time?

[Philip Amadeo Saeli]

* Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]:
 I have a column in a table which is epoch time including milliseconds.
 
 e.g. = 1409304102153 
 
 
 now i want to display all fields in the table but this field as: 2014-8-29 
 Fri 09:21:42: GMT  (whatever comes in )
 
 
 and i am not finding anything on web about how to do that.
 
 can anyone help please.
 
 ty.
 Rajeev

I do not know how to do it directly in MySQL, but if you can dump the
table and post-process, this may be helpful on Linux:  The date(1) cmd
can translate between formats, e.g. (taking the above value),

  date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N
  2014-08-29 Fri 04:21:42.15300

--Phil

-- 
Philip Amadeo Saeli
openSUSE, CentOS, RHEL
psa...@zorodyne.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

MySQL 5.7 release plan/date

[Igor Dvorzhak]

Hi all,

When MySQL 5.7 planned to be released as stable/production ready?

Best,
Igor

Re: MySQL 5.7 release plan/date

[Morgan Tocker]

Hi Igor,

 When MySQL 5.7 planned to be released as stable/production ready?

We do not provide planned release dates.

If I can recommend a video to watch about the release process, please watch 
Tomas Ulin’s Percona Live keynote April 2013:
http://www.youtube.com/watch?v=OpHTV59I1gs

Watch from 4m30 onwards.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Date comparison help

[Michael Stroh]

I recently upgraded a local MySQL installation to 5.5.32 and am trying to 
figure out why the following query won't work as expected anymore. I'm just 
trying to compare a set of dates to NOW() but since the upgrade, these don't 
seem to work as expected.

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY), 
NOW(), 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)NOW()

For instance, when I run it on my system, I get 1 for the third column even 
though comparing the two by eye it should be false.

Cheers,
Michael

Re: Date comparison help

[kitlenv]

Hi Michael,

FYI: I'm using 5.6.13 and your query returns 0 for the third column with my
instance.

Cheers,
Sam


On Wed, Oct 23, 2013 at 2:35 AM, Michael Stroh st...@astroh.org wrote:

 I recently upgraded a local MySQL installation to 5.5.32 and am trying to
 figure out why the following query won't work as expected anymore. I'm just
 trying to compare a set of dates to NOW() but since the upgrade, these
 don't seem to work as expected.

 SELECT
 DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
 NOW(),
 DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2
 DAY)NOW()

 For instance, when I run it on my system, I get 1 for the third column
 even though comparing the two by eye it should be false.

 Cheers,
 Michael

Re: Date comparison help

[Michael Stroh]

Thanks Sam.

It turns out that if I put the DATE_ADD.. within DATE(), it works as expected. 
That is sufficient for my goals, but it would be nice to understand this issue 
in case there may be other cases that I need to watch out for.

Cheers,
Michael



On Oct 22, 2013, at 6:18 PM, kitlenv kitl...@gmail.com wrote:

 Hi Michael,
 
 FYI: I'm using 5.6.13 and your query returns 0 for the third column with my 
 instance. 
 
 Cheers,
 Sam
 
 
 On Wed, Oct 23, 2013 at 2:35 AM, Michael Stroh st...@astroh.org wrote:
 I recently upgraded a local MySQL installation to 5.5.32 and am trying to 
 figure out why the following query won't work as expected anymore. I'm just 
 trying to compare a set of dates to NOW() but since the upgrade, these don't 
 seem to work as expected.
 
 SELECT
 DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
 NOW(),
 DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 
 DAY)NOW()
 
 For instance, when I run it on my system, I get 1 for the third column even 
 though comparing the two by eye it should be false.
 
 Cheers,
 Michael
 
 
 

Re: Date comparison help

[hsv]

 2013/10/22 12:20 -0400,  
I recently upgraded a local MySQL installation to 5.5.32 and am trying to 
figure out why the following query won't work as expected anymore. I'm just 
trying to compare a set of dates to NOW() but since the upgrade, these don't 
seem to work as expected.

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY), 
NOW(), 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)NOW()

For instance, when I run it on my system, I get 1 for the third column even 
though comparing the two by eye it should be false.

Well, show us all three columns

And with 5.5.8 I get the same third column as you. Has it worked?

And I found that changed to

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) AS 
A, 
NOW(), 
CAST(DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) 
AS DATETIME)NOW() AS B

it works as hoped for--and it seems a bug to me, but probably an old one. It 
seems to me that the outcome of DATE_ADD is DATE, not DATETIME, and the 
comparison is numeric, with the six trailing 0s dropped. Quote about 
STR_TO_DATE:
It takes a string str and a format string format. 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_str-to-dateSTR_TO_DATE()
 returns a 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATETIME
 value if the format string contains both date and time parts, or a 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetimeDATE
 or 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#timeTIME
 value if the string contains only date or time parts. 
How really does it decide which type to return? It is wrong if the decision is 
based whether all the hour, minute, and second are 0 or not.  


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

From DATE_FORMAT and back to origin date in mysql date column

[Karl-Arne Gjersøyen]

SELECT DATE_FORMAT(dato, '%e/%c/%Y') AS dato FROM transportdokument WHERE
dato = '2013-07-20' AND dato = '2013-07-24' GROUP BY dato DESC is working
perfect in my PHP file.

But I need to transfer the date back from my norwegian formatted date to
the origin date format in WHERE dato = '$standard_date_format';

What need I do to fix this?

Thanks for your time and help to learn me programming!

Karl

RE: From DATE_FORMAT and back to origin date in mysql date column

[Rick James]

I'm unclear on your task, but maybe this function will help:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

(It is confusing to have dato as both a column name and an alias.)

 -Original Message-
 From: Karl-Arne Gjersøyen [mailto:karlar...@gmail.com]
 Sent: Wednesday, July 24, 2013 7:02 AM
 To: MySQL Mailinglist
 Subject: From DATE_FORMAT and back to origin date in mysql date column
 
 SELECT DATE_FORMAT(dato, '%e/%c/%Y') AS dato FROM transportdokument WHERE
 dato = '2013-07-20' AND dato = '2013-07-24' GROUP BY dato DESC is
 working perfect in my PHP file.
 
 But I need to transfer the date back from my norwegian formatted date to
 the origin date format in WHERE dato = '$standard_date_format';
 
 What need I do to fix this?
 
 Thanks for your time and help to learn me programming!
 
 Karl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Retrieve the values from the table of its max date

[hsv]

 2012/11/29 11:46 +0530, Trimurthy 
i have a table which contains the columns 
date,sname,age,item,quantity,units.my question is i want to retrieve all 
the values from the table where date=maxdate group by sname how can i get 
those values.

A question, I suspect, found in all SQL courses


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Retrieve the values from the table of its max date

[Rick James]

and answered many times on forums.mysql.com

 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Thursday, November 29, 2012 5:40 PM
 To: mysql@lists.mysql.com
 Subject: Re: Retrieve the values from the table of its max date
 
  2012/11/29 11:46 +0530, Trimurthy 
 i have a table which contains the columns
 date,sname,age,item,quantity,units.my question is i want to retrieve
 all the values from the table where date=maxdate group by sname how can
 i get those values.
 
 A question, I suspect, found in all SQL courses
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Retrieve the values from the table of its max date

[walter harms]

perhaps you are looking for something like

select entry,timestamp from table A where A.timestamp=(select max(B.timestamp) 
from table B where a.entry=b.entry);

also this oage may be helpful:
http://www.artfulsoftware.com/infotree/queries.php

re,
 wh


Am 30.11.2012 02:39, schrieb h...@tbbs.net:
 2012/11/29 11:46 +0530, Trimurthy 
 i have a table which contains the columns 
 date,sname,age,item,quantity,units.my question is i want to retrieve all 
 the values from the table where date=maxdate group by sname how can i get 
 those values.
 
 A question, I suspect, found in all SQL courses
 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/15 00:30 +0100, Mogens Melander 
I guess I'm sill learning.

Does that mean that, if the last column in a load blabla. is a -00-00
terminated by ^n it might error ? Or are we talking ODBC ?

Find it under LOAD DATA 

If an empty field is parsed for a NOT NULL DATE or DATETIME, instead of 
reporting error as in strict mode, the parser makes the date -00-00--Maybe 
in ODBC, too, which I do not know, but certainly from character input. (I 
regularly use CSV files with MySQL, although the match is not perfect.) This is 
a particular case of zero (see LOAD DATA) for any NOT NULL type.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[sagar bs]

tried to import data as text, but its showing Operation failed with
exitcode 1

On Wed, Nov 14, 2012 at 1:12 PM, Mogens Melander mog...@fumlersoft.dkwrote:

 Or you could import the date as text and convert dates using:

 mysql SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
 - '2004-04-31'


 On Wed, November 14, 2012 06:13, Larry Martell wrote:
  On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
  Hi,
 
  As i have the data with some 25 variables in csv file and i need to
  import
  to mysql.
  The issue is that the date format in csv file is dd/mm/ and mysql
  takes
  the date format like /mm/dd.
  The  number of variables in the csv file are same in the table in
  database
  of mysql.
  Please help me out.
 
  Convert the data in your CSV file into the format you need. So many
  ways to do that, e.g.: bring in into excel and change the column's
  format, use sed, use python, use vi, 


 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
Regards

SAGAR B S

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Mogens Melander]

Did you change the target column to varchar before import ?

On Wed, November 14, 2012 10:23, sagar bs wrote:
 tried to import data as text, but its showing Operation failed with
 exitcode 1

 On Wed, Nov 14, 2012 at 1:12 PM, Mogens Melander
 mog...@fumlersoft.dkwrote:

 Or you could import the date as text and convert dates using:

 mysql SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
 - '2004-04-31'


 On Wed, November 14, 2012 06:13, Larry Martell wrote:
  On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
  Hi,
 
  As i have the data with some 25 variables in csv file and i need to
  import
  to mysql.
  The issue is that the date format in csv file is dd/mm/ and mysql
  takes
  the date format like /mm/dd.
  The  number of variables in the csv file are same in the table in
  database
  of mysql.
  Please help me out.
 
  Convert the data in your CSV file into the format you need. So many
  ways to do that, e.g.: bring in into excel and change the column's
  format, use sed, use python, use vi, 


 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




 --
 Regards

 SAGAR B S

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/14 10:26 +0530, sagar bs 
As i have the data with some 25 variables in csv file and i need to import
to mysql.
The issue is that the date format in csv file is dd/mm/ and mysql takes
the date format like /mm/dd.
The  number of variables in the csv file are same in the table in database
of mysql.
Please help me out.

Use LOAD DATA s feature of in the same SQL statement importing into a user 
variable and using it with SET, using the function STR_TO_DATE:

load data ...
(..., @dait, ...) ...
SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y')

. It is not important how many decimal digits match each pattern, but it cannot 
match variation in the separators.

It is required that NULLs in the file take the form '\N' or 'NULL', depending 
on escape-option.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[sagar bs]

Hi,


There are four  columns in my table named like account_name, c1, c2 and c3.
Account name is the primary key and c1, c2 contain two different dates and
in the column c2 there are few fields  showing /00/00,  now i need to
get the date different(in days)
between the dates present in the c1 and c2. That days should be shown in
the c3. please help me out.

On Wed, Nov 14, 2012 at 3:46 PM, h...@tbbs.net wrote:

  2012/11/14 10:26 +0530, sagar bs 
 As i have the data with some 25 variables in csv file and i need to import
 to mysql.
 The issue is that the date format in csv file is dd/mm/ and mysql takes
 the date format like /mm/dd.
 The  number of variables in the csv file are same in the table in database
 of mysql.
 Please help me out.
 
 Use LOAD DATA s feature of in the same SQL statement importing into a user
 variable and using it with SET, using the function STR_TO_DATE:

 load data ...
 (..., @dait, ...) ...
 SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y')

 . It is not important how many decimal digits match each pattern, but it
 cannot match variation in the separators.

 It is required that NULLs in the file take the form '\N' or 'NULL',
 depending on escape-option.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
Regards

SAGAR B S

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[hsv]

 2012/11/14 18:27 +0530, sagar bs 
There are four  columns in my table named like account_name, c1, c2 and c3. 
Account name is the primary key and c1, c2 contain two different dates and in 
the column c2 there are few fields  showing /00/00,  now i need to get the 
date different(in days)
between the dates present in the c1 and c2. That days should be shown in the 
c3. please help me out. 

Try DATEDIFF.

As for date /00/00, MySQL s treatment of NULLs in CSV files is peculiar: it 
wants the escape NULL or \N, separator right after separator is not NULL, but 
empty string. Consider those NULL.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Mogens Melander]

Dude, which part of RTFM did yoy miss?

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

On Wed, November 14, 2012 13:57, sagar bs wrote:
 Hi,


 There are four  columns in my table named like account_name, c1, c2 and
 c3.
 Account name is the primary key and c1, c2 contain two different dates and
 in the column c2 there are few fields  showing /00/00,  now i need to
 get the date different(in days)
 between the dates present in the c1 and c2. That days should be shown in
 the c3. please help me out.

 On Wed, Nov 14, 2012 at 3:46 PM, h...@tbbs.net wrote:

  2012/11/14 10:26 +0530, sagar bs 
 As i have the data with some 25 variables in csv file and i need to
 import
 to mysql.
 The issue is that the date format in csv file is dd/mm/ and mysql
 takes
 the date format like /mm/dd.
 The  number of variables in the csv file are same in the table in
 database
 of mysql.
 Please help me out.
 
 Use LOAD DATA s feature of in the same SQL statement importing into a
 user
 variable and using it with SET, using the function STR_TO_DATE:

 load data ...
 (..., @dait, ...) ...
 SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y')

 . It is not important how many decimal digits match each pattern, but it
 cannot match variation in the separators.

 It is required that NULLs in the file take the form '\N' or 'NULL',
 depending on escape-option.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




 --
 Regards

 SAGAR B S

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Mogens Melander]

I guess I'm sill learning.

Does that mean that, if the last column in a load blabla. is a -00-00
terminated by ^n it might error ? Or are we talking ODBC ?

On Wed, November 14, 2012 18:58, h...@tbbs.net wrote:
 2012/11/14 18:27 +0530, sagar bs 
 There are four  columns in my table named like account_name, c1, c2 and
 c3. Account name is the primary key and c1, c2 contain two different dates
 and in the column c2 there are few fields  showing /00/00,  now i need
 to get the date different(in days)
 between the dates present in the c1 and c2. That days should be shown in
 the c3. please help me out.
 
 Try DATEDIFF.

 As for date /00/00, MySQL s treatment of NULLs in CSV files is
 peculiar: it wants the escape NULL or \N, separator right after separator
 is not NULL, but empty string. Consider those NULL.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Mogens Melander]

6:40 AM: Sorry, didn't mean to be rude. It's in there.

On Thu, November 15, 2012 00:23, Mogens Melander wrote:
 Dude, which part of RTFM did yoy miss?

 http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

 On Wed, November 14, 2012 13:57, sagar bs wrote:
 Hi,


 There are four  columns in my table named like account_name, c1, c2 and
 c3.
 Account name is the primary key and c1, c2 contain two different dates
 and
 in the column c2 there are few fields  showing /00/00,  now i need
 to
 get the date different(in days)
 between the dates present in the c1 and c2. That days should be shown in
 the c3. please help me out.

 On Wed, Nov 14, 2012 at 3:46 PM, h...@tbbs.net wrote:

  2012/11/14 10:26 +0530, sagar bs 
 As i have the data with some 25 variables in csv file and i need to
 import
 to mysql.
 The issue is that the date format in csv file is dd/mm/ and mysql
 takes
 the date format like /mm/dd.
 The  number of variables in the csv file are same in the table in
 database
 of mysql.
 Please help me out.
 
 Use LOAD DATA s feature of in the same SQL statement importing into a
 user
 variable and using it with SET, using the function STR_TO_DATE:

 load data ...
 (..., @dait, ...) ...
 SET Sins = STR_TO_DATE(@dait, '%d/%m/%Y')

 . It is not important how many decimal digits match each pattern, but
 it
 cannot match variation in the separators.

 It is required that NULLs in the file take the form '\N' or 'NULL',
 depending on escape-option.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




 --
 Regards

 SAGAR B S

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.




 --
 Mogens Melander
 +66 8701 33224

 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Larry Martell]

On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
 Hi,

 As i have the data with some 25 variables in csv file and i need to import
 to mysql.
 The issue is that the date format in csv file is dd/mm/ and mysql takes
 the date format like /mm/dd.
 The  number of variables in the csv file are same in the table in database
 of mysql.
 Please help me out.

Convert the data in your CSV file into the format you need. So many
ways to do that, e.g.: bring in into excel and change the column's
format, use sed, use python, use vi, 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Issue regarding the import of the date from csv file to the table in the database in mysql

[Mogens Melander]

Or you could import the date as text and convert dates using:

mysql SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
- '2004-04-31'


On Wed, November 14, 2012 06:13, Larry Martell wrote:
 On Tue, Nov 13, 2012 at 9:56 PM, sagar bs bs.sag...@gmail.com wrote:
 Hi,

 As i have the data with some 25 variables in csv file and i need to
 import
 to mysql.
 The issue is that the date format in csv file is dd/mm/ and mysql
 takes
 the date format like /mm/dd.
 The  number of variables in the csv file are same in the table in
 database
 of mysql.
 Please help me out.

 Convert the data in your CSV file into the format you need. So many
 ways to do that, e.g.: bring in into excel and change the column's
 format, use sed, use python, use vi, 


-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: date-IFNULL-sum bug?

[hsv]

 2012/10/08 14:52 -0700, Rick James 
Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR

No, those operations are well defined. Amongst the timestamp-functions there is 
constant reference to numeric context, and character context--and well there 
is, because there are no time-constants, only numerals and character strings 
taken for timestamps. It is also the only means of doing some things.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: date-IFNULL-sum bug?

[Rick James]

Do not use + for DATE arithmetic!
Use, for example
  + INTERVAL 1 YEAR


 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Thursday, October 04, 2012 9:35 PM
 To: mysql@lists.mysql.com
 Subject: date-IFNULL-sum bug?
 
 Can anyone explain this to me?
 The first one seems quite wrong; the rest make perfect sense.
 
 mysql select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
 +--+
 | ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
 +--+
 |11900 |
 +--+
 1 row in set (0.00 sec)
 
 mysql select ifnull(date('1900/5/3'), date('1900/01/01'));
 +--+
 | ifnull(date('1900/5/3'), date('1900/01/01')) |
 +--+
 | 1900-05-03   |
 +--+
 1 row in set (0.00 sec)
 
 mysql select date('1900/5/3') + 1;
 +--+
 | date('1900/5/3') + 1 |
 +--+
 | 19010503 |
 +--+
 1 row in set (0.00 sec)
 
 mysql select date(date('1900/5/3') + 1);
 ++
 | date(date('1900/5/3') + 1) |
 ++
 | 1901-05-03 |
 ++
 1 row in set (0.00 sec)
 
 (5.5.8 under muSoft Windows)
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

date-IFNULL-sum bug?

[hsv]

Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.

mysql select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)

mysql select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03   |
+--+
1 row in set (0.00 sec)

mysql select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)

mysql select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)

(5.5.8 under muSoft Windows)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: date comparison query

[Hal�sz S�ndor]

 2012/03/16 13:30 -0400, Simon Wilkinson 
My query for this is as follows: select * from table where table.date1 -
table.date2 between 28425600 and 29030400;

I would not count on that subtraction s yielding a meaningful number: the types 
are not Unix timestamps. I would use TIMESTAMPDIFF, with a good unit.

 From the downloaded help file:


Date arithmetic also can be performed using INTERVAL together with the 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_plus+
 or 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_minus-
 operator: 


date + INTERVAL expr unit
date - INTERVAL expr unit



but subtracting timestamp from timestamp hoping for a while (interval) is not 
mentioned.
If you want the subtraction to work, make them big integers and use 
UNIX_TIMESTAMP and FROM_UNIXTIME. See
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

date comparison query

[Simon Wilkinson]

Hi,

I have a table that holds two datetime columns.  I am trying to find values
from this table that fall into specific time ranges, but am getting some
strange results.  For example, if I try to find rows where the difference
between the two column is between 47 and 48 weeks, I get back a result
where the actual difference is less than 1 month.

My query for this is as follows: select * from table where table.date1 -
table.date2 between 28425600 and 29030400;

The result returns a row where date1 is 2010-10-31 18:24:49, and date2
is 2010-10-02 20:29:54.

I seem to get proper results for some values (I am trying to find results
that fall into different weekly ranges), but then some are just way off.
 Does anybody have any ideas for why this is happening?

Thanks,

Simon

Re: date comparison query

[Baron Schwartz]

Simon,

It's likely that when you specify the times as integer literals they
are being converted to something you don't expect. You can use EXPLAIN
EXTENDED followed by SHOW WARNINGS to see what's happening; it might
be interesting and educational. I would specify the times you want as
datetime literals, in 'quotes', to make sure the database interprets
your values correctly.

On Fri, Mar 16, 2012 at 1:30 PM, Simon Wilkinson
simon.wilkin...@gmail.com wrote:
 Hi,

 I have a table that holds two datetime columns.  I am trying to find values
 from this table that fall into specific time ranges, but am getting some
 strange results.  For example, if I try to find rows where the difference
 between the two column is between 47 and 48 weeks, I get back a result
 where the actual difference is less than 1 month.

 My query for this is as follows: select * from table where table.date1 -
 table.date2 between 28425600 and 29030400;

 The result returns a row where date1 is 2010-10-31 18:24:49, and date2
 is 2010-10-02 20:29:54.

 I seem to get proper results for some values (I am trying to find results
 that fall into different weekly ranges), but then some are just way off.
  Does anybody have any ideas for why this is happening?

 Thanks,

 Simon



-- 
Baron Schwartz
Win free MySQL conference tickets! http://goo.gl/mvZ4W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Getting data from 2 tables if records have same date!

[Shawn L Green]

On 2/29/2012 5:54 PM, LUCi5R wrote:

JW,



I'm trying to understand LEFT JOIN as we go - but it's not working.



This query



SELECT *

FROM CUSTOMERS

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)



Is giving me some results which I'm not quite sure what they are - but it's
not the right results.



The way I'm testing is, on 02/28/12 I had 57 Customers created in the
CUSTOMERS table.

I also had a total of 105 Calls recorded in the CALLS table. Some calls were
from the same customers more then once.



Essentially, I need the result to be 86 which I got from some manual
calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
but not in the CALLS table. The other 85 were in both tables.



The above LEFT JOIN query gave me 69 records and quite a few duplicate
entries. I'm trying to dissect it to understand what exactly it selected.



Thanks!



~~
LUCi5R
e:  luc...@luci5r.com
w:  http://www.luci5r.com





From: Johnny Withers [mailto:joh...@pixelated.net]
Sent: Wednesday, February 29, 2012 1:30 PM
To: luc...@luci5r.com
Cc: mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!



Sounds like you need to LEFT JOIN:



SELECT *

FROM CUSTOMERS

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12

WHERE CUSTOMERS.DATE = 02/28/12



But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.



This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:



SELECT *

FROM CUSTOMERS

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)



Try this:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE
WHERE CUSTOMERS.DATE = 02/28/12

This will give you a list of all customers for a given date and a list 
of every call they made on that date.  If a customer made no calls on a 
date, then all of the columns for that table will be NULL.


If you only want a list of customers and details about the calls on a 
date then an INNER JOIN is appropriate.  If you want to see the full 
list of customers and any calls on that date use this:


SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CUSTOMERS.DATE=CALLS.DATE = 
02/28/12

If you only want a list of customers that made any calls on a given date, you 
can use the EXISTS comparator like this:

SELECT customers.*
FROM customers
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND 
CUSTOMERS.DATE=CALLS.DATE = 02/28/12)

http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

It's possible to get you any combination of data you want, we just need you to 
clarify the relationship you are trying to find and how much data you really 
want to get back.

NOTE: the name of the column date is using a reserved word. You may want to enclose it in 
backticks to avoid confusion as in `date`. Also, the standard MySQL syntax for date 
literals uses ISO notation. So instead of using 02/28/12 (using double 
quotes) I expected to see '2012-02-28' (using single quotes)

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Getting data from 2 tables if records have same date!

[David Giragosian]

On Thu, Mar 1, 2012 at 8:57 AM, Shawn L Green shawn.l.gr...@oracle.comwrote:

 On 2/29/2012 5:54 PM, LUCi5R wrote:

 JW,



 I'm trying to understand LEFT JOIN as we go - but it's not working.



 This query



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

 WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
 02/28/12)



 Is giving me some results which I'm not quite sure what they are - but
 it's
 not the right results.



 The way I'm testing is, on 02/28/12 I had 57 Customers created in the
 CUSTOMERS table.

 I also had a total of 105 Calls recorded in the CALLS table. Some calls
 were
 from the same customers more then once.



 Essentially, I need the result to be 86 which I got from some manual
 calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
 but not in the CALLS table. The other 85 were in both tables.



 The above LEFT JOIN query gave me 69 records and quite a few duplicate
 entries. I'm trying to dissect it to understand what exactly it selected.



 Thanks!



 ~~
 LUCi5R
 e:  luc...@luci5r.com
 w:  http://www.luci5r.com





 From: Johnny Withers [mailto:joh...@pixelated.net]
 Sent: Wednesday, February 29, 2012 1:30 PM
 To: luc...@luci5r.com
 Cc: mysql@lists.mysql.com
 Subject: Re: Getting data from 2 tables if records have same date!



 Sounds like you need to LEFT JOIN:



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE =
 02/28/12

 WHERE CUSTOMERS.DATE = 02/28/12



 But that would only get customers created on 2/28 AND having a call on
 2/28
 OR not call at all on 2/28.



 This would give you customers created on 2/28 with no calls AND customers
 created on 2/28 with a call on 2/28:



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

 WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
 02/28/12)


 Try this:

 SELECT *
 FROM CUSTOMERS
 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
 CUSTOMERS.DATE=CALLS.DATE
 WHERE CUSTOMERS.DATE = 02/28/12

 This will give you a list of all customers for a given date and a list of
 every call they made on that date.  If a customer made no calls on a date,
 then all of the columns for that table will be NULL.

 If you only want a list of customers and details about the calls on a date
 then an INNER JOIN is appropriate.  If you want to see the full list of
 customers and any calls on that date use this:

 SELECT *
 FROM CUSTOMERS
 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
 CUSTOMERS.DATE=CALLS.DATE = 02/28/12

 If you only want a list of customers that made any calls on a given date,
 you can use the EXISTS comparator like this:

 SELECT customers.*
 FROM customers
 WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
 CUSTOMERS.DATE=CALLS.DATE = 02/28/12)

 http://dev.mysql.com/doc/**refman/5.5/en/exists-and-not-**
 exists-subqueries.htmlhttp://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

 It's possible to get you any combination of data you want, we just need
 you to clarify the relationship you are trying to find and how much data
 you really want to get back.

 NOTE: the name of the column date is using a reserved word. You may want
 to enclose it in backticks to avoid confusion as in `date`. Also, the
 standard MySQL syntax for date literals uses ISO notation. So instead of
 using 02/28/12 (using double quotes) I expected to see '2012-02-28'
 (using single quotes)

 --
 Shawn Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN



Another gem from Shawn. ;-)

RE: Getting data from 2 tables if records have same date!

[LUCi5R]

JW,

You’re correct .. that is what I was getting with the LEFT JOIN and
therefore it wasn’t the correct answer. 

I was able to get the correct answer using UNION ALL, however, like you
said, I needed 2 queries in that case. One to get the PHONE numbers from
both tables … and a 2nd query to get all the CUSTOMERS matching the PHONE
numbers from the CUSTOMERS table. Although I still have a few issues with
that (since I can’t use a VIEW cause VIEWS don’t allow subqueries in SELECT
statements; and I don’t really want to create a new table every time this
query is run) … but anyhow, this is the UNION ALL query that got the correct
“86” records result:

SELECT PHONE FROM (
 (SELECT PHONE,DATE FROM CUSTOMERS)
UNION ALL
 (SELECT PHONE,DATE FROM CALLS)
)
results
WHERE DATE = “02/28/12”
GROUP BY PHONE;

I would still like to explore the possibility of doing this using 1 single
query which gives me the results I need – rather than first getting the
correct PHONE numbers; inserting them in a temporary table; and then pulling
records out of CUSTOMERS matching those PHONE numbers. 

THANKS!

~~
LUCi5R
e:  luc...@luci5r.com
w:  http://www.luci5r.com


From: Johnny Withers [mailto:joh...@pixelated.net] 
Sent: Wednesday, February 29, 2012 3:15 PM
To: luc...@luci5r.com
Cc: mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!

After looking at this again, the query you are using;

SELECT * 
FROM CUSTOMERS 
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 
WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

Would return all customers in the customer's table created on 2/28/12 WITH
no calls or a call on 2/28.

Maybe you should try..

SELECT * 
FROM CUSTOMERS 
WHERE CUSTOMERS.DATE = 02/28/12 

UNION

SELECT *
FROM CUSTOMERS
INNER JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 
WHERE CALLS.DATE = 02/28/12

Basically, you have to do two queries to get the data you want. The WHERE
customers.date=2/28 is only getting those customers created on 2/28
regardless if they had a call or not on 2/28. So if you had a customer
created on 2/27 and a call on 2/28, the query we were using is not going to
pick that customer up.

If you want duplicate customers from the second query in the UNION above,
you can use UNION ALL instead of just UNION between the queries.

-JW

On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R luc...@luci5r.com wrote:
JW,
 
I’m trying to understand LEFT JOIN as we go – but it’s not working. 
 
This query 
 
SELECT * 
FROM CUSTOMERS 
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 
WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)
 
Is giving me some results which I’m not quite sure what they are – but it’s
not the right results. 
 
The way I’m testing is, on 02/28/12 I had 57 Customers created in the
CUSTOMERS table. 
I also had a total of 105 Calls recorded in the CALLS table. Some calls were
from the same customers more then once. 
 
Essentially, I need the result to be 86 which I got from some manual
calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
but not in the CALLS table. The other 85 were in both tables. 
 
The above LEFT JOIN query gave me 69 records and quite a few duplicate
entries. I’m trying to dissect it to understand what exactly it selected. 
 
Thanks!
 
~~
LUCi5R
e:  luc...@luci5r.com
w:  http://www.luci5r.com
 
 
From: Johnny Withers [mailto:joh...@pixelated.net] 
Sent: Wednesday, February 29, 2012 1:30 PM
To: luc...@luci5r.com
Cc: mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!
 
Sounds like you need to LEFT JOIN:
 
SELECT * 
FROM CUSTOMERS 
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12
WHERE CUSTOMERS.DATE = 02/28/12 
 
But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.
 
This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:
 
SELECT * 
FROM CUSTOMERS 
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 
WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)
 
JW
 
On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote:
Guys,

I've been working with MySQL for a while (been on  off this list over the
last 10 years or so); I'm definitely not a n00b and have worked with SQL
extensively. Used JOIN and all quite a bit ... but I haven't done
subqueries, union or nested joins.

I'm completely stumped on this problem; and Google hasn't been helpful at
all. I'll try to be as descriptive as possible.

I have 2 tables ... CUSTOMERS and CALLS.
Think of Customers table as your Directory. It has the customer's contact
information  some other information. In total about 20 fields in there.
The Calls table has only about 7 fields. Each time a customer calls in, the
conversation details gets recorded in this Calls table.

The PHONE field is the key field that joins the CUSTOMERS  CALLS

RE: Getting data from 2 tables if records have same date!

[LUCi5R]

SELECT *
FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE =
02/28/12 OR CALLS.DATE = 02/28/12

^^^ This is going into an endless loop; I'm not getting any result at all.
I'm not sure why. I haven't used USING before so I need to read up a bit on
that to understand what you're doing here. 

Thanks!

 ~~
 LUCi5R
 e:  luc...@luci5r.com
 w:  http://www.luci5r.com

-Original Message-
From: Halász Sándor [mailto:h...@tbbs.net] 
Sent: Wednesday, February 29, 2012 2:57 PM
To: mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!

; 2012/02/29 15:29 -0600, Johnny Withers  Sounds like you need to
LEFT JOIN:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12
WHERE CUSTOMERS.DATE = 02/28/12

But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.

This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE WHERE CUSTOMERS.DATE =
02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

Exactly; but I believe that this is the right thing:

SELECT *
FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE) WHERE CUSTOMERS.DATE =
02/28/12 OR CALLS.DATE = 02/28/12

If you have a hit --PHONE found in both tables--, you will get a record if
either date matches, and I believe that you wanted that. If it is a miss
--there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not
equal and only CUSTOMERS.DATE will match a date.

And if this works, surely it is clear where to put BETWEEN.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Getting data from 2 tables if records have same date!

[LUCi5R]

-Original Message-
From: Shawn L Green [mailto:shawn.l.gr...@oracle.com] 
Sent: Thursday, March 01, 2012 6:57 AM
To: luc...@luci5r.com
Cc: 'Johnny Withers'; mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!

Try this:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE WHERE CUSTOMERS.DATE = 02/28/12

This will give you a list of all customers for a given date and a list of
every call they made on that date.  If a customer made no calls on a date,
then all of the columns for that table will be NULL.

If you only want a list of customers and details about the calls on a date
then an INNER JOIN is appropriate.  If you want to see the full list of
customers and any calls on that date use this:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = 02/28/12

If you only want a list of customers that made any calls on a given date,
you can use the EXISTS comparator like this:

SELECT customers.*
FROM customers
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = 02/28/12)

http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

It's possible to get you any combination of data you want, we just need you
to clarify the relationship you are trying to find and how much data you
really want to get back.

NOTE: the name of the column date is using a reserved word. You may want to
enclose it in backticks to avoid confusion as in `date`. Also, the standard
MySQL syntax for date literals uses ISO notation. So instead of using
02/28/12 (using double quotes) I expected to see '2012-02-28' (using
single quotes)

--
Shawn Green
MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and
Software, Engineered to Work Together.
Office: Blountville, TN

~~

Shawn,

I still need to try your queries but I wanted to quickly get back to you on
a couple of things. 

You're correct! The DATE column is a reserved word and in my queries I do
indeed use `backticks`. Interestingly, I never knew that's what they are
called!!! I used to call that symbol `Grave Accent` ... never heard it being
called `backticks`. Good to know :)

Secondly, unfortunately this DATE field came from an original FoxPro
Database (DBF) which was an Char (8) field and got translated into mysql as
such. So unfortunately it's not a DATE field ... it's a Char (8) field. For
simplicity, I've been using Date = 02/28/12 to explain my queries here,
but in reality, in my program, I've actually been using the STR_TO_DATE()
function as such ... 

STR_TO_DATE(`DATE`, '%m/%d/%Y') = STR_TO_DATE('02/2/12', '%m/%d/%Y')

Lastly, you're correct again, I don't actually use the Double Quotes around
the dates ... I do use Single Quotes. 

In regards to the relationship/data that I'm looking for ... this is what
I'm looking for ...

I need to pull the records from the CUSTOMERS table, where 
a) The customer was created on given date (Eg:- '02/28/12')
b) The customer called on given date and the call was recorded in the Calls
table (Eg:- '02/28/12')

Let me give your queries a shot  understand them!

THANKS!!

~~
 LUCi5R
 e:  luc...@luci5r.com
 w:  http://www.luci5r.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Getting data from 2 tables if records have same date!

[Shawn L Green]

Stupid wrapping helped me to make a simple mistake. I wrote

On 3/1/2012 10:40 AM, LUCi5R wrote:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = 02/28/12

But I meant it to be

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
  AND CALLS.DATE = 02/28/12

But based on your description:

a) The customer was created on given date (Eg:- '02/28/12')
b) The customer called on given date and the call was recorded in the Calls
table


There are possibly two different dates at play, a creation date 
(customers.date) and an activity date (calls.date). Therefore, we need to list them 
separately. Also, you said you wanted just the CUSTOMERS records (without any call 
details) so I assume you only want to see a single copy of each customer. This would work 
best using the EXISTS pattern I provided last with a simple modification:

SELECT customers.*
FROM customers
WHERE EXISTS (SELECT * FROM calls WHERE CUSTOMERS.PHONE = CALLS.PHONE AND
CUSTOMERS.DATE=CALLS.DATE = activity date)
AND customers.date=creation date

There are many other ways to find this same set of data. Here is a two-step 
process using an indexed temporary table (technically, it's a 3-step process as 
you need to drop the explicit temp table, too).

CREATE TEMPORARY TABLE tmp_custs(key(phone) USINB BTREE) ENGINE=MEMORY SELECT DISTINCT 
phone FROM CALLS WHERE date=activity date;

SELECT customers.*
FROM customers
INNER JOIN tmp_custs ON tmp_custs.phone = customers.phone
WHERE customers.date = create date;

DROP TEMPORARY TABLE tmp_custs;

By default the MEMORY engine creates all indexes as HASH indexes. So in order to replace 
the =activity date comparison with any sort of ranged comparison, you need a 
BTREE index.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Getting data from 2 tables if records have same date!

[Johnny Withers]

Sounds like you need to LEFT JOIN:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12
WHERE CUSTOMERS.DATE = 02/28/12

But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.

This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

JW

On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote:

 Guys,

 I've been working with MySQL for a while (been on  off this list over the
 last 10 years or so); I'm definitely not a n00b and have worked with SQL
 extensively. Used JOIN and all quite a bit ... but I haven't done
 subqueries, union or nested joins.

 I'm completely stumped on this problem; and Google hasn't been helpful at
 all. I'll try to be as descriptive as possible.

 I have 2 tables ... CUSTOMERS and CALLS.
 Think of Customers table as your Directory. It has the customer's contact
 information  some other information. In total about 20 fields in there.
 The Calls table has only about 7 fields. Each time a customer calls in,
 the conversation details gets recorded in this Calls table.

 The PHONE field is the key field that joins the CUSTOMERS  CALLS tables.
 That is the only identifying key that gets written on the Calls record when
 that customer calls.

 One thing to note -- It is possible for a customer to exist in the
 CUSTOMERS table, but not exist in the CALLS table; however, it is not
 possible for a PHONE # to be in the CALLS table but not in CUSTOMERS table.
 Essentially, a customer's record has to be created first in the CUSTOMERS
 table before a call can be recorded from him in the CALLS table.

 Also, CALLS table can have multiple entries with same PHONE # (Customer
 called many times - maybe even same day), but CUSTOMERS will only have a
 single entry for a PHONE #.

 Here comes my problem ...

 I have a PHONE SEARCH box with the ability to define a date range; for
 simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for
 now.

 When someone searches for a PHONE number, I want to show ALL the CUSTOMERS
 that:
 a. Were CREATED on that day (Date defined in Search Criteria)
 b. Had CALLED in that day (Date defined in Search Criteria)

 The DATA that I need to pull up and show is in the CUSTOMERS table; not
 the CALLS table -- so the DATA I need needs to come out of the CUSTOMERS
 table matching on phone from both tables for the given DATE.

 In other words - any CUSTOMER that has the PHONE NUMBER which appears  in
 BOTH CUSTOMERS  CALLS table with the DATE defined should pull up.

 For the life of me - I can't get this to work!!
 Let's take the date 02/28/12 for example sake.

 My biggest issue is ... using JOIN, I can pull up ...
 a. ALL the phone/customers that appeared in the CALLS table with date
 02/28/12
 b. ALL the phone/customers that appeared in CALLS  CUSTOMERS with date
 02/28/12

 BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in
 CALLS table at all - does NOT show up!! And that is because I'm using
 CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't
 pick up a record where the phone didn't exist in both tables.

 My initial query was:

 SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
 WHERE CUSTOMERS.DATE = 02/28/12 AND CALLS.DATE = 02/28/12

 I've tried 100's of combinations of this query; many different OR, AND,
 GROUP BY combinations --- but I can't pull up ALL records from CUSTOMERS
 with DATE 02/28/12 and ALL records from CALLS with DATE 02/28/12 in a
 single query.

 I've hit a wall here.

 Any ideas/suggestions/advice?

 THANKS

 ~~
 LUCi5R
 e:  luc...@luci5r.com
 w:  http://www.luci5r.com



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

RE: Getting data from 2 tables if records have same date!

[LUCi5R]

JW,

 

I'm trying to understand LEFT JOIN as we go - but it's not working. 

 

This query 

 

SELECT * 

FROM CUSTOMERS 

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 

WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

 

Is giving me some results which I'm not quite sure what they are - but it's
not the right results. 

 

The way I'm testing is, on 02/28/12 I had 57 Customers created in the
CUSTOMERS table. 

I also had a total of 105 Calls recorded in the CALLS table. Some calls were
from the same customers more then once. 

 

Essentially, I need the result to be 86 which I got from some manual
calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
but not in the CALLS table. The other 85 were in both tables. 

 

The above LEFT JOIN query gave me 69 records and quite a few duplicate
entries. I'm trying to dissect it to understand what exactly it selected. 

 

Thanks!

 

~~
LUCi5R
e:  luc...@luci5r.com
w:  http://www.luci5r.com

 

 

From: Johnny Withers [mailto:joh...@pixelated.net] 
Sent: Wednesday, February 29, 2012 1:30 PM
To: luc...@luci5r.com
Cc: mysql@lists.mysql.com
Subject: Re: Getting data from 2 tables if records have same date!

 

Sounds like you need to LEFT JOIN:

 

SELECT * 

FROM CUSTOMERS 

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12

WHERE CUSTOMERS.DATE = 02/28/12 

 

But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.

 

This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:

 

SELECT * 

FROM CUSTOMERS 

LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE 

WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

 

JW

 

On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote:

Guys,

I've been working with MySQL for a while (been on  off this list over the
last 10 years or so); I'm definitely not a n00b and have worked with SQL
extensively. Used JOIN and all quite a bit ... but I haven't done
subqueries, union or nested joins.

I'm completely stumped on this problem; and Google hasn't been helpful at
all. I'll try to be as descriptive as possible.

I have 2 tables ... CUSTOMERS and CALLS.
Think of Customers table as your Directory. It has the customer's contact
information  some other information. In total about 20 fields in there.
The Calls table has only about 7 fields. Each time a customer calls in, the
conversation details gets recorded in this Calls table.

The PHONE field is the key field that joins the CUSTOMERS  CALLS tables.
That is the only identifying key that gets written on the Calls record when
that customer calls.

One thing to note -- It is possible for a customer to exist in the CUSTOMERS
table, but not exist in the CALLS table; however, it is not possible for a
PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a
customer's record has to be created first in the CUSTOMERS table before a
call can be recorded from him in the CALLS table.

Also, CALLS table can have multiple entries with same PHONE # (Customer
called many times - maybe even same day), but CUSTOMERS will only have a
single entry for a PHONE #.

Here comes my problem ...

I have a PHONE SEARCH box with the ability to define a date range; for
simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for
now.

When someone searches for a PHONE number, I want to show ALL the CUSTOMERS
that:
a. Were CREATED on that day (Date defined in Search Criteria)
b. Had CALLED in that day (Date defined in Search Criteria)

The DATA that I need to pull up and show is in the CUSTOMERS table; not the
CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table
matching on phone from both tables for the given DATE.

In other words - any CUSTOMER that has the PHONE NUMBER which appears  in
BOTH CUSTOMERS  CALLS table with the DATE defined should pull up.

For the life of me - I can't get this to work!!
Let's take the date 02/28/12 for example sake.

My biggest issue is ... using JOIN, I can pull up ...
a. ALL the phone/customers that appeared in the CALLS table with date
02/28/12
b. ALL the phone/customers that appeared in CALLS  CUSTOMERS with date
02/28/12

BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in
CALLS table at all - does NOT show up!! And that is because I'm using
CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't
pick up a record where the phone didn't exist in both tables.

My initial query was:

SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
WHERE CUSTOMERS.DATE = 02/28/12 AND CALLS.DATE = 02/28/12

I've tried 100's of combinations of this query; many different OR, AND,
GROUP BY combinations --- but I can't pull up ALL records from CUSTOMERS
with DATE 02/28/12 and ALL records from CALLS with DATE 02/28/12 in a
single query.

I've hit a wall here.

Any ideas

Re: Getting data from 2 tables if records have same date!

[Andrés Tello]

what about

select customers.* from customers left join calls on
(customers.date=calls.date) where customers.date=02/28/12;

of course date should be an index in both tables.

I think it migth work

On Wed, Feb 29, 2012 at 4:54 PM, LUCi5R luc...@luci5r.com wrote:

 JW,



 I'm trying to understand LEFT JOIN as we go - but it's not working.



 This query



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

 WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
 02/28/12)



 Is giving me some results which I'm not quite sure what they are - but it's
 not the right results.



 The way I'm testing is, on 02/28/12 I had 57 Customers created in the
 CUSTOMERS table.

 I also had a total of 105 Calls recorded in the CALLS table. Some calls
 were
 from the same customers more then once.



 Essentially, I need the result to be 86 which I got from some manual
 calculations. Out of those 86 records, 1 record is in the CUSTOMERS table
 but not in the CALLS table. The other 85 were in both tables.



 The above LEFT JOIN query gave me 69 records and quite a few duplicate
 entries. I'm trying to dissect it to understand what exactly it selected.



 Thanks!



 ~~
 LUCi5R
 e:  luc...@luci5r.com
 w:  http://www.luci5r.com





 From: Johnny Withers [mailto:joh...@pixelated.net]
 Sent: Wednesday, February 29, 2012 1:30 PM
 To: luc...@luci5r.com
 Cc: mysql@lists.mysql.com
 Subject: Re: Getting data from 2 tables if records have same date!



 Sounds like you need to LEFT JOIN:



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE =
 02/28/12

 WHERE CUSTOMERS.DATE = 02/28/12



 But that would only get customers created on 2/28 AND having a call on 2/28
 OR not call at all on 2/28.



 This would give you customers created on 2/28 with no calls AND customers
 created on 2/28 with a call on 2/28:



 SELECT *

 FROM CUSTOMERS

 LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE

 WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
 02/28/12)



 JW



 On Wed, Feb 29, 2012 at 1:02 PM, LUCi5R luc...@luci5r.com wrote:

 Guys,

 I've been working with MySQL for a while (been on  off this list over the
 last 10 years or so); I'm definitely not a n00b and have worked with SQL
 extensively. Used JOIN and all quite a bit ... but I haven't done
 subqueries, union or nested joins.

 I'm completely stumped on this problem; and Google hasn't been helpful at
 all. I'll try to be as descriptive as possible.

 I have 2 tables ... CUSTOMERS and CALLS.
 Think of Customers table as your Directory. It has the customer's contact
 information  some other information. In total about 20 fields in there.
 The Calls table has only about 7 fields. Each time a customer calls in, the
 conversation details gets recorded in this Calls table.

 The PHONE field is the key field that joins the CUSTOMERS  CALLS tables.
 That is the only identifying key that gets written on the Calls record when
 that customer calls.

 One thing to note -- It is possible for a customer to exist in the
 CUSTOMERS
 table, but not exist in the CALLS table; however, it is not possible for a
 PHONE # to be in the CALLS table but not in CUSTOMERS table. Essentially, a
 customer's record has to be created first in the CUSTOMERS table before a
 call can be recorded from him in the CALLS table.

 Also, CALLS table can have multiple entries with same PHONE # (Customer
 called many times - maybe even same day), but CUSTOMERS will only have a
 single entry for a PHONE #.

 Here comes my problem ...

 I have a PHONE SEARCH box with the ability to define a date range; for
 simplicity sake - we'll use just One Date instead of DATE ... BETWEEN for
 now.

 When someone searches for a PHONE number, I want to show ALL the CUSTOMERS
 that:
 a. Were CREATED on that day (Date defined in Search Criteria)
 b. Had CALLED in that day (Date defined in Search Criteria)

 The DATA that I need to pull up and show is in the CUSTOMERS table; not the
 CALLS table -- so the DATA I need needs to come out of the CUSTOMERS table
 matching on phone from both tables for the given DATE.

 In other words - any CUSTOMER that has the PHONE NUMBER which appears  in
 BOTH CUSTOMERS  CALLS table with the DATE defined should pull up.

 For the life of me - I can't get this to work!!
 Let's take the date 02/28/12 for example sake.

 My biggest issue is ... using JOIN, I can pull up ...
 a. ALL the phone/customers that appeared in the CALLS table with date
 02/28/12
 b. ALL the phone/customers that appeared in CALLS  CUSTOMERS with date
 02/28/12

 BUT -- If there's a customer with date 02/28/12 who DOES NOT appear in
 CALLS table at all - does NOT show up!! And that is because I'm using
 CUSTOMERS.PHONE=CALLS.PHONE in the JOIN ON clause. So it obviously won't
 pick up a record where the phone didn't exist in both tables.

 My initial query was:

 SELECT * FROM CUSTOMERS JOIN CALLS ON CUSTOMERS.PHONE

Re: Getting data from 2 tables if records have same date!

[Hal�sz S�ndor]

; 2012/02/29 15:29 -0600, Johnny Withers 
Sounds like you need to LEFT JOIN:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE AND CALLS.DATE = 02/28/12
WHERE CUSTOMERS.DATE = 02/28/12

But that would only get customers created on 2/28 AND having a call on 2/28
OR not call at all on 2/28.

This would give you customers created on 2/28 with no calls AND customers
created on 2/28 with a call on 2/28:

SELECT *
FROM CUSTOMERS
LEFT JOIN CALLS ON CUSTOMERS.PHONE = CALLS.PHONE
WHERE CUSTOMERS.DATE = 02/28/12 AND (CALLS.PHONE IS NULL OR CALLS.DATE =
02/28/12)

Exactly; but I believe that this is the right thing:

SELECT *
FROM CUSTOMERS LEFT JOIN CALLS USING(PHONE)
WHERE CUSTOMERS.DATE = 02/28/12 OR CALLS.DATE = 02/28/12

If you have a hit --PHONE found in both tables--, you will get a record if 
either date matches, and I believe that you wanted that. If it is a miss 
--there is no CALLS-record for the PHONE-- CALLS.DATE will be NULL and not 
equal and only CUSTOMERS.DATE will match a date.

And if this works, surely it is clear where to put BETWEEN.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: setting the default of a date field

[william drescher]

On 1/27/2012 6:00 PM, Peter Brawley wrote:

On 1/27/2012 2:24 PM, william drescher wrote:

On 1/27/2012 3:21 PM, Peter Brawley wrote:

On 1/27/2012 11:18 AM, william drescher wrote:

Is there a way to set the default of a date field to the date
the record is generated ?

bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB


When I try
ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview`
`lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
I get:
#1067 - Invalid default value for 'lastQuarterlyReview'


Yes, if you want a default value use a timestamp column.



Thanks Peter


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: setting the default of a date field

[Peter Brawley]

On 1/27/2012 11:18 AM, william drescher wrote:
Is there a way to set the default of a date field to the date the 
record is generated ?


bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: setting the default of a date field

[william drescher]

On 1/27/2012 3:21 PM, Peter Brawley wrote:

On 1/27/2012 11:18 AM, william drescher wrote:

Is there a way to set the default of a date field to the date
the record is generated ?

bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB


When I try
ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` 
`lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP

I get:
#1067 - Invalid default value for 'lastQuarterlyReview'

bill


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: setting the default of a date field

[Peter Brawley]

On 1/27/2012 2:24 PM, william drescher wrote:

On 1/27/2012 3:21 PM, Peter Brawley wrote:

On 1/27/2012 11:18 AM, william drescher wrote:

Is there a way to set the default of a date field to the date
the record is generated ?

bill



Use a TIMESTAMP. Default DATE(TIME) columns with Triggers.

PB


When I try
ALTER TABLE `ptInfo` CHANGE `lastQuarterlyReview` 
`lastQuarterlyReview` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP

I get:
#1067 - Invalid default value for 'lastQuarterlyReview'


Yes, if you want a default value use a timestamp column.

PB

-



bill




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Date and Time

[Donovan Brooke]

Hello, I'm doing an insert into with date and time type fields.

I was reading:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

My question is: is the format always 'year month day'?.. or can we save 
dates in 'month day year' as well?


Thanks,
Donovan


--
D Brooke

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Andrew Moore]

What's your problem/reason with how it is?

Andy

On Sun, Jan 8, 2012 at 8:21 PM, Donovan Brooke li...@euca.us wrote:

 Hello, I'm doing an insert into with date and time type fields.

 I was reading:
 http://dev.mysql.com/doc/**refman/5.1/en/date-and-time-**literals.htmlhttp://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

 My question is: is the format always 'year month day'?.. or can we save
 dates in 'month day year' as well?

 Thanks,
 Donovan


 --
 D Brooke

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Peter Brawley]

On 1/8/2012 2:21 PM, Donovan Brooke wrote:

Hello, I'm doing an insert into with date and time type fields.

I was reading:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

My question is: is the format always 'year month day'?.. or can we 
save dates in 'month day year' as well?
As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format 
date strings to the format MySQL uses.


PB

-



Thanks,
Donovan




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Donovan Brooke]

Peter Brawley wrote:

On 1/8/2012 2:21 PM, Donovan Brooke wrote:

Hello, I'm doing an insert into with date and time type fields.

I was reading:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html

My question is: is the format always 'year month day'?.. or can we
save dates in 'month day year' as well?

As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format
date strings to the format MySQL uses.

PB

-


Thanks!

Donovan




--
D Brooke

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Govinda]

 What's your problem/reason with how it is?

I assume Andy means:

leave it stored as a timestamp type or datetime type, and when you need to 
display it otherwise.. then covert with date()

-G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Paul DuBois]

On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote:

 Hello, I'm doing an insert into with date and time type fields.
 
 I was reading:
 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html
 
 My question is: is the format always 'year month day'?.. or can we save dates 
 in 'month day year' as well?


In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on 
year-month-day order.

If you want to store a value in a different format, you must use some other 
data type such as VARCHAR. But then it won't be interpreted as a date.

If you want to display a date from a DATE, etc. column in some other format, 
pass the value to DATE_FORMAT().

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

If you want to reformat a date value in some other format to put it in 
year-month-day format so that you can store it in a DATE, etc. column, 
STR_TO_DATE() might be helpful.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

STR_TO_DATE() can be useful, for example, when loading non year-month-day data 
into a table with LOAD DATA. You can use STR_TO_DATE() to reformat the values 
on the fly.

LOAD DATA LOCAL INFILE 'data.txt' 
  INTO TABLE t (name,@date,value) 
  SET date = STR_TO_DATE(@date,'%m/%d/%y'); 

-- 
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Date and Time

[Govinda]

 
 leave it stored as a timestamp type or datetime type, and when you need to 
 display it otherwise.. then covert with date()

oops, Paul's post reminded me I was suggesting a PHP function here ^^^  ... and 
this is the MySQL list.


 -G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Deleting of records older than a specific date time

[Machiel Richards]

Hi Everyone

I have posted this question quite a while back and noticed now
that I haven't gotten an answer as yet and this is still on my
unresolved list.

We have 2 tables which we want to archive data from.

We need to be able to delete all data from the 2 tables that is
older than the start of yesterday.

 The process of determining the rows manually is easy enough,
however we would like to cut out manual intervention and automate this
process.

 I have played around with trying to create a stored routine /
function which is then passed the date as a parameter and then to use
this to determine the rows and delete them.

   - This created 2 problems for me though

  -- initially I struggled with the date format
because it does not want to accept the date format.
  -- when at last it seemed to accept the date
format , it caused the script to not exit upon completion.

  I am sure there must be a better way to do this than using
functions and linux scripts to automate the process, however my
experience is failing me in this respect as I am fairly new to the dba
scene.


   I would really appreciate it if someone can assist me in this
regard please.

Regards
Machiel

PS someone already suggested me using MySQL scheduled events ,
however I was not able to figure out how to use it for this
functionality though.

Re: Deleting of records older than a specific date time

[John Daisley]

A simple stored procedure run by the event scheduler at predetermined times
will do this quite easily. Can you post the output of SHOW CREATE TABLE for
your two tables?

John

On 23 May 2011 13:15, Machiel Richards machi...@rdc.co.za wrote:

 Hi Everyone

I have posted this question quite a while back and noticed now
 that I haven't gotten an answer as yet and this is still on my
 unresolved list.

We have 2 tables which we want to archive data from.

We need to be able to delete all data from the 2 tables that is
 older than the start of yesterday.

 The process of determining the rows manually is easy enough,
 however we would like to cut out manual intervention and automate this
 process.

 I have played around with trying to create a stored routine /
 function which is then passed the date as a parameter and then to use
 this to determine the rows and delete them.

   - This created 2 problems for me though

  -- initially I struggled with the date format
 because it does not want to accept the date format.
  -- when at last it seemed to accept the date
 format , it caused the script to not exit upon completion.

  I am sure there must be a better way to do this than using
 functions and linux scripts to automate the process, however my
 experience is failing me in this respect as I am fairly new to the dba
 scene.


   I would really appreciate it if someone can assist me in this
 regard please.

 Regards
 Machiel

 PS someone already suggested me using MySQL scheduled events ,
 however I was not able to figure out how to use it for this
 functionality though.







-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

Re: Deleting of records older than a specific date time

[a . smith]

Quoting Machiel Richards machi...@rdc.co.za:




We need to be able to delete all data from the 2 tables that is
older than the start of yesterday.



Hi,

  I use this ver simple script to purge data from a syslog DB:

#!/usr/local/bin/bash
/usr/local/bin/mysql -u syslog -pmypasswd -e 'DELETE FROM SystemEvents  
WHERE ReceivedAt  date_add(current_date, interval -60 day)' Syslog


maybe that is useful for you...?

cheers Andy.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Strange date behaviour

[Andrew Braithwaite]

Hi,

Mysql  select curdate() + interval 6 month - interval 6 month;
+-+
| curdate() + interval 6 month - interval 6 month |
+-+
| 2011-03-30  |
+-+
1 row in set (0.00 sec)

Any ideas why this is wrong?

Andrew

-
LOVEFiLM UK Limited is a company registered in England and Wales. 
Registered Number: 06528297. 
Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom.

This e-mail is confidential to the ordinary user of the e-mail address to which 
it was addressed. If you have received it in error, 
please delete it from your system and notify the sender immediately.

This email message has been delivered safely and archived online by Mimecast.
For more information please visit http://www.mimecast.co.uk 
-

Re: Strange date behaviour

[Roy Lyseng]

On 31.03.11 12.52, Andrew Braithwaite wrote:

Hi,

Mysql  select curdate() + interval 6 month - interval 6 month;
+-+
| curdate() + interval 6 month - interval 6 month |
+-+
| 2011-03-30  |
+-+
1 row in set (0.00 sec)

Any ideas why this is wrong?


Interval arithmetic is tricky...

curdate() is 2011-03-31, which is the last date of March.
Adding 6 months gives the last date of september, ie 2011-09-30, subtracting 6 
months gives 2011-03-30.


My best recommendation is don't do it.

Hope this helps,
Roy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Get date from unix_timestamp only up to the hour

[Bryan Cantwell]

How would I go about modifying a unix timestamp to actually represent 
the 'top of the hour' that it represents?

For instance:
1296158500 = 1/27/2011 2:01:40 PM
That is in the 2:00 pm hour, how can I find that out and modify it to 
1296158400 which = 1/27/2011 2:00:00 PM?





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Get date from unix_timestamp only up to the hour

[Nathan Sullivan]

Bryan,

Maybe something like this would work?

select 1296158500 - (1296158500 % 3600)


Hope that helps,

Nathan

On Thu, Feb 24, 2011 at 08:41:58AM -0800, Bryan Cantwell wrote:
 How would I go about modifying a unix timestamp to actually represent 
 the 'top of the hour' that it represents?
 For instance:
 1296158500 = 1/27/2011 2:01:40 PM
 That is in the 2:00 pm hour, how can I find that out and modify it to 
 1296158400 which = 1/27/2011 2:00:00 PM?
 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Get date from unix_timestamp only up to the hour

[Michael Dykman]

If the timestmp is in seconds, the result is simply   mod(timestamp,3600)

 - michael dykman

On Thu, Feb 24, 2011 at 11:41 AM, Bryan Cantwell
bcantw...@firescope.com wrote:
 How would I go about modifying a unix timestamp to actually represent the
 'top of the hour' that it represents?
 For instance:
 1296158500 = 1/27/2011 2:01:40 PM
 That is in the 2:00 pm hour, how can I find that out and modify it to
 1296158400 which = 1/27/2011 2:00:00 PM?




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Get date from unix_timestamp only up to the hour

[Pintér Tibor]

On 02/24/2011 05:41 PM, Bryan Cantwell wrote:
 How would I go about modifying a unix timestamp to actually represent
 the 'top of the hour' that it represents?
 For instance:
 1296158500 = 1/27/2011 2:01:40 PM
 That is in the 2:00 pm hour, how can I find that out and modify it to
 1296158400 which = 1/27/2011 2:00:00 PM?

something like this:

mysql set @now:=now(), @foo:=unix_timestamp(); select @now, @foo, @foo
- minute(@now) * 60 - second(@now) as hour_unix, from_unixtime(@foo -
minute(@now) * 60 - second(@now));
Query OK, 0 rows affected (0.00 sec)

+-++++
| @now| @foo   | hour_unix  | from_unixtime(@foo -
minute(@now) * 60 - second(@now)) |
+-++++
| 2011-02-24 18:06:24 | 1298567184 | 1298566800 | 2011-02-24 18:00:00
 |
+-++++
1 row in set (0.00 sec)



t

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Get date from unix_timestamp only up to the hour

[Bryan Cantwell]

Yes perfect! Thanks, I knew I was over thinking this.

On 02/24/2011 10:56 AM, Nathan Sullivan wrote:

Bryan,

Maybe something like this would work?

select 1296158500 - (1296158500 % 3600)


Hope that helps,

Nathan

On Thu, Feb 24, 2011 at 08:41:58AM -0800, Bryan Cantwell wrote:

How would I go about modifying a unix timestamp to actually represent
the 'top of the hour' that it represents?
For instance:
1296158500 = 1/27/2011 2:01:40 PM
That is in the 2:00 pm hour, how can I find that out and modify it to
1296158400 which = 1/27/2011 2:00:00 PM?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Get date from unix_timestamp only up to the hour

[Pintér Tibor]

On 02/24/2011 05:56 PM, Nathan Sullivan wrote:
 Bryan,
 
 Maybe something like this would work?
 
 select 1296158500 - (1296158500 % 3600)

ah, yes, even this one:

mysql select now() - interval (unix_timestamp() % 3600) second;
+---+
| now() - interval (unix_timestamp() % 3600) second |
+---+
| 2011-02-24 18:00:00   |
+---+
1 row in set (0.00 sec)

t

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Help with Date in Where Clause

[Phillip Baker]

Greetings All,

I am looking for a little help in setting a where clause.
I have a dateAdded field that is a DATETIME field.
I am looking to pull records from Midnight to midnight the previous day.
I thought just passing the date (without time) would get it but I keep
getting an empty record set.
So looking for something that works a bit better.

Any suggestions?

Blessed Be

Phillip

Never ascribe to malice what can be explained by incompetence
-- Hanlon's Razor

Re: Help with Date in Where Clause

[Jørn Dahl-Stamnes]

On Monday 31 January 2011 21:12, Phillip Baker wrote:
 Greetings All,

 I am looking for a little help in setting a where clause.
 I have a dateAdded field that is a DATETIME field.
 I am looking to pull records from Midnight to midnight the previous day.
 I thought just passing the date (without time) would get it but I keep
 getting an empty record set.
 So looking for something that works a bit better.

select * from your_table where convert(dateAdded, date)='2011-01-31';

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with Date in Where Clause

[Phillip Baker]

Thank you very much Jørn

Blessed Be

Phillip

Never ascribe to malice what can be explained by incompetence
-- Hanlon's Razor


On Mon, Jan 31, 2011 at 1:18 PM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:

 Jørn

Re: Help with Date in Where Clause

[Shawn Green (MySQL)]

On 1/31/2011 15:12, Phillip Baker wrote:

Greetings All,

I am looking for a little help in setting a where clause.
I have a dateAdded field that is a DATETIME field.
I am looking to pull records from Midnight to midnight the previous day.
I thought just passing the date (without time) would get it but I keep
getting an empty record set.
So looking for something that works a bit better.

Any suggestions?

Blessed Be

Phillip

Never ascribe to malice what can be explained by incompetence
-- Hanlon's Razor



All of the datetime values for yesterday actually exist as a range of 
datetime values between midnight that morning (inclusive) and midnight 
the next morning (not part of the search). So your WHERE clause needs to 
resemble


... WHERE dtcolumn = '2011-01-21 00:00:00' and dtcolumn  '2011-01-22 
00:00:00'


This pattern has the added advantage of not eliminating the possibility 
of using an INDEX on the dtcolumn column by wrapping it inside a function.


Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with Date in Where Clause

[Bruce Ferrell]

On 01/31/2011 12:18 PM, Jørn Dahl-Stamnes wrote:
 On Monday 31 January 2011 21:12, Phillip Baker wrote:
   
 Greetings All,

 I am looking for a little help in setting a where clause.
 I have a dateAdded field that is a DATETIME field.
 I am looking to pull records from Midnight to midnight the previous day.
 I thought just passing the date (without time) would get it but I keep
 getting an empty record set.
 So looking for something that works a bit better.
 
 select * from your_table where convert(dateAdded, date)='2011-01-31';

   
not so good, but it works:

select * from your_table where dateAdded like '2011-01-31%';

OR
select * from your_table where dateAdded between '2011-01-30%' and
'2011-01-31%';


better:

select * from your_table where DATE_SUB('2011-01-31', INTERVAL 1 DAY);

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Loading date takes a long time after replication

[Sairam Krishnamurthy]

Any pointers on this issue ?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 01/03/2011 01:51 AM, Ananda Kumar wrote:

What is the output of show full processlist

On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy 
kmsram...@gmail.com mailto:kmsram...@gmail.com wrote:


Hi all,

I set up replication some days back. Because of some internal
issues we canceled the replication and reset the master to be a
normal database server. But after this revert, the database is
unusually slow.

It was slow because of the discussion below when replication was
on. But I wonder why it is slow now after the master has been
reset. By reset I mean, commenting out the master setup lines in
the my.cnf file, removal of logs and restarting the database.

Thanks in advance


Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to
SAN storage and it was quite fast.

regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman
vegiv...@tuxera.be mailto:vegiv...@tuxera.be
mailto:vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote:

   Are you saying that mass inserts go much slower now that
you've set up
   replication? In that case, I suspect you have your binlogs
on the
   same disk
   as your data.

   Put the binary logs on separate disks, and you'll notice a
   dramatic increase
   in performance.


   On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
kmsram...@gmail.com mailto:kmsram...@gmail.com
mailto:kmsram...@gmail.com mailto:kmsram...@gmail.comwrote:


 All,

 I have a situation. We have a very large database(gigabytes).
   When we load
 the data in to the table, it will be huge again (20 million
   rows). So in
 order to get good backup we set up a replication(master-slave
   setup). But
 now, because of this setup, loading into the table takes hours
   which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for
   hours for
 the table to be loaded. Should we abandon replication for this?
   Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




   --
   Bier met grenadyn
   Is als mosterd by den wyn
   Sy die't drinkt, is eene kwezel
   Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Sairam Krishnamurthy]

Hi all,

I set up replication some days back. Because of some internal issues we 
canceled the replication and reset the master to be a normal database 
server. But after this revert, the database is unusually slow.


It was slow because of the discussion below when replication was on. But 
I wonder why it is slow now after the master has been reset. By reset I 
mean, commenting out the master setup lines in the my.cnf file, removal 
of logs and restarting the database.


Thanks in advance

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to SAN 
storage and it was quite fast.


regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman vegiv...@tuxera.be 
mailto:vegiv...@tuxera.be wrote:


Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the
same disk
as your data.

Put the binary logs on separate disks, and you'll notice a
dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
kmsram...@gmail.com mailto:kmsram...@gmail.comwrote:

 All,

 I have a situation. We have a very large database(gigabytes).
When we load
 the data in to the table, it will be huge again (20 million
rows). So in
 order to get good backup we set up a replication(master-slave
setup). But
 now, because of this setup, loading into the table takes hours
which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for
hours for
 the table to be loaded. Should we abandon replication for this?
Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Ananda Kumar]

What is the output of show full processlist

On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy kmsram...@gmail.comwrote:

 Hi all,

 I set up replication some days back. Because of some internal issues we
 canceled the replication and reset the master to be a normal database
 server. But after this revert, the database is unusually slow.

 It was slow because of the discussion below when replication was on. But I
 wonder why it is slow now after the master has been reset. By reset I mean,
 commenting out the master setup lines in the my.cnf file, removal of logs
 and restarting the database.

 Thanks in advance


 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161


 On 12/06/2010 04:47 AM, Ananda Kumar wrote:

 Also, make sure your /tmp folder is on a separate and fast disk.
 We had similar issues and we moved /tmp folder from Local to SAN storage
 and it was quite fast.

 regards
 anandkl

 On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman vegiv...@tuxera.bemailto:
 vegiv...@tuxera.be wrote:

Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the
same disk
as your data.

Put the binary logs on separate disks, and you'll notice a
dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
kmsram...@gmail.com mailto:kmsram...@gmail.comwrote:


 All,

 I have a situation. We have a very large database(gigabytes).
When we load
 the data in to the table, it will be huge again (20 million
rows). So in
 order to get good backup we set up a replication(master-slave
setup). But
 now, because of this setup, loading into the table takes hours
which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for
hours for
 the table to be loaded. Should we abandon replication for this?
Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Sairam Krishnamurthy]

Apparently nothing. Just the Show process list query.

 70100 | user | localhost | database | Query   |0 | NULL  | 
show full processlist


Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 01/03/2011 01:51 AM, Ananda Kumar wrote:

What is the output of show full processlist

On Mon, Jan 3, 2011 at 1:10 PM, Sairam Krishnamurthy 
kmsram...@gmail.com mailto:kmsram...@gmail.com wrote:


Hi all,

I set up replication some days back. Because of some internal
issues we canceled the replication and reset the master to be a
normal database server. But after this revert, the database is
unusually slow.

It was slow because of the discussion below when replication was
on. But I wonder why it is slow now after the master has been
reset. By reset I mean, commenting out the master setup lines in
the my.cnf file, removal of logs and restarting the database.

Thanks in advance


Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to
SAN storage and it was quite fast.

regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman
vegiv...@tuxera.be mailto:vegiv...@tuxera.be
mailto:vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote:

   Are you saying that mass inserts go much slower now that
you've set up
   replication? In that case, I suspect you have your binlogs
on the
   same disk
   as your data.

   Put the binary logs on separate disks, and you'll notice a
   dramatic increase
   in performance.


   On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
kmsram...@gmail.com mailto:kmsram...@gmail.com
mailto:kmsram...@gmail.com mailto:kmsram...@gmail.comwrote:


 All,

 I have a situation. We have a very large database(gigabytes).
   When we load
 the data in to the table, it will be huge again (20 million
   rows). So in
 order to get good backup we set up a replication(master-slave
   setup). But
 now, because of this setup, loading into the table takes hours
   which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for
   hours for
 the table to be loaded. Should we abandon replication for this?
   Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




   --
   Bier met grenadyn
   Is als mosterd by den wyn
   Sy die't drinkt, is eene kwezel
   Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Johan De Meersman]

Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the same disk
as your data.

Put the binary logs on separate disks, and you'll notice a dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy kmsram...@gmail.comwrote:

 All,

 I have a situation. We have a very large database(gigabytes). When we load
 the data in to the table, it will be huge again (20 million rows). So in
 order to get good backup we set up a replication(master-slave setup). But
 now, because of this setup, loading into the table takes hours which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for hours for
 the table to be loaded. Should we abandon replication for this? Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Ananda Kumar]

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to SAN storage and
it was quite fast.

regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 Are you saying that mass inserts go much slower now that you've set up
 replication? In that case, I suspect you have your binlogs on the same disk
 as your data.

 Put the binary logs on separate disks, and you'll notice a dramatic
 increase
 in performance.


 On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy kmsram...@gmail.com
 wrote:

  All,
 
  I have a situation. We have a very large database(gigabytes). When we
 load
  the data in to the table, it will be huge again (20 million rows). So in
  order to get good backup we set up a replication(master-slave setup). But
  now, because of this setup, loading into the table takes hours which will
 be
  generally done in a few minutes otherwise.
 
  Is there a workaround for this? We really don't want to wait for hours
 for
  the table to be loaded. Should we abandon replication for this? Any
 pointers
  ?
 
  --
  Thanks,
  Sairam Krishnamurthy
  +1 612 859 8161
 
 


 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Sairam Krishnamurthy]

Thanks Guys. Let me check these options and will get back with the 
results. Thanks again.


Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 12/06/2010 04:47 AM, Ananda Kumar wrote:

Also, make sure your /tmp folder is on a separate and fast disk.
We had similar issues and we moved /tmp folder from Local to SAN 
storage and it was quite fast.


regards
anandkl

On Mon, Dec 6, 2010 at 4:10 PM, Johan De Meersman vegiv...@tuxera.be 
mailto:vegiv...@tuxera.be wrote:


Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the
same disk
as your data.

Put the binary logs on separate disks, and you'll notice a
dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy
kmsram...@gmail.com mailto:kmsram...@gmail.comwrote:

 All,

 I have a situation. We have a very large database(gigabytes).
When we load
 the data in to the table, it will be huge again (20 million
rows). So in
 order to get good backup we set up a replication(master-slave
setup). But
 now, because of this setup, loading into the table takes hours
which will be
 generally done in a few minutes otherwise.

 Is there a workaround for this? We really don't want to wait for
hours for
 the table to be loaded. Should we abandon replication for this?
Any pointers
 ?

 --
 Thanks,
 Sairam Krishnamurthy
 +1 612 859 8161




--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Raj Shekhar]

In infinite wisdom Sairam Krishnamurthy kmsram...@gmail.com wrote:

 [1  text/plain; ISO-8859-1 (7bit)]
 All,
 
 I have a situation. We have a very large database(gigabytes). When we 
 load the data in to the table, it will be huge again (20 million rows). 
 So in order to get good backup we set up a replication(master-slave 
 setup). But now, because of this setup, loading into the table takes 
 hours which will be generally done in a few minutes otherwise.

What part of the system is busy the most when you load the data - is it
the CPU or the disk or the network?  Replication should not slow down
the speed of the insert, this might just be a red herring and the
problem might be somewhere else.
 
-- 
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.
- 
Read the latest at my blog: Humor in the bookstore 
http://rajshekhar.net/blog/archives/391-Humor-in-the-bookstore.html



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Loading date takes a long time after replication

[Sairam Krishnamurthy]

All,

I have a situation. We have a very large database(gigabytes). When we 
load the data in to the table, it will be huge again (20 million rows). 
So in order to get good backup we set up a replication(master-slave 
setup). But now, because of this setup, loading into the table takes 
hours which will be generally done in a few minutes otherwise.


Is there a workaround for this? We really don't want to wait for hours 
for the table to be loaded. Should we abandon replication for this? Any 
pointers ?


--
Thanks,
Sairam Krishnamurthy
+1 612 859 8161

Re: Not to show until a certain date

[Joerg Bruehe]

Hi!


Hank wrote:
 On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
 b...@biz-comm.com wrote:
 On 9/28/10 8:33 PM, Chris W wrote:

 SELECT *
 FROM announcements
 WHERE announcements_expiredate  CURDATE()
 AND announcements_postdate = CURDATE()
 ORDER BY announcements_expiredate ASC
 
 Or how about something like this:
 
 SELECT *
 FROM announcements
 WHERE  CURDATE() between announcements_postdate and announcements_expiredate
 ORDER BY announcements_expiredate ASC

The syntax is correct, but I don't think this statement will be
optimized as well as the other proposal:
BETWEEN is intended for column BETWEEN const1 AND const2,
whereas your statement is const BETWEEN column1 AND column2.


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Not to show until a certain date

[Hank]

On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe joerg.bru...@oracle.com wrote:
 Hi!


 Hank wrote:
 On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
 b...@biz-comm.com wrote:
 On 9/28/10 8:33 PM, Chris W wrote:

 SELECT *
 FROM announcements
 WHERE announcements_expiredate  CURDATE()
 AND announcements_postdate = CURDATE()
 ORDER BY announcements_expiredate ASC

 Or how about something like this:

 SELECT *
 FROM announcements
 WHERE  CURDATE() between announcements_postdate and announcements_expiredate
 ORDER BY announcements_expiredate ASC

 The syntax is correct, but I don't think this statement will be
 optimized as well as the other proposal:
 BETWEEN is intended for column BETWEEN const1 AND const2,
 whereas your statement is const BETWEEN column1 AND column2.


But that only really matters if there are indexes on the column1 and
column2 fields.

And for the optimizer, wouldn't it make sense to map BETWEEN into two
comparison statements (columnconst1 and column=const2)  or
(constcolumn1 and const=column2) where both scenarios the
optimizer may be able to use indexes on the fields?  It's exactly the
same as the other proposal:

CURDATE()  announcements_postdate and CURDATE()=
announcements_expiredate which still is using two different fields
for the comparisons... so wouldn't both scenarios end up in the exact
same place?

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Not to show until a certain date

[BMBasal]

Your suggestion seems more elegant. However, you missed the mathematical
meaning of BETWEEN in SQL: 
it is inclusive of both lower and upper bounds.

In the case raised by Patrice Olivier-Wilson, when an announcement expires
on announcements_expiredate, it should not show on that date, and
thereafter.

But using BETWEEN, it will show on announcements_expiredate, thus a logical
error.

-Original Message-
From: Hank [mailto:hes...@gmail.com] 
Sent: Friday, October 01, 2010 4:10 PM
To: Joerg Bruehe
Cc: mysql@lists.mysql.com
Subject: Re: Not to show until a certain date

On Fri, Oct 1, 2010 at 2:34 PM, Joerg Bruehe joerg.bru...@oracle.com
wrote:
 Hi!


 Hank wrote:
 On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson
 b...@biz-comm.com wrote:
 On 9/28/10 8:33 PM, Chris W wrote:

 SELECT *
 FROM announcements
 WHERE announcements_expiredate  CURDATE()
 AND announcements_postdate = CURDATE()
 ORDER BY announcements_expiredate ASC

 Or how about something like this:

 SELECT *
 FROM announcements
 WHERE  CURDATE() between announcements_postdate and
announcements_expiredate
 ORDER BY announcements_expiredate ASC

 The syntax is correct, but I don't think this statement will be
 optimized as well as the other proposal:
 BETWEEN is intended for column BETWEEN const1 AND const2,
 whereas your statement is const BETWEEN column1 AND column2.


But that only really matters if there are indexes on the column1 and
column2 fields.

And for the optimizer, wouldn't it make sense to map BETWEEN into two
comparison statements (columnconst1 and column=const2)  or
(constcolumn1 and const=column2) where both scenarios the
optimizer may be able to use indexes on the fields?  It's exactly the
same as the other proposal:

CURDATE()  announcements_postdate and CURDATE()=
announcements_expiredate which still is using two different fields
for the comparisons... so wouldn't both scenarios end up in the exact
same place?

-Hank

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@cs.albany.edu


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org