List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 13 2006 6:09pm
Subject:Re: Problems with timestamp field after upgrading MySQL Server.
View as plain text  
Alternatively, you might be able to re-render times and dates in their 
"condensed" format by auto-converting them to a numeric value. Try adding 
zero to your date columns in your select clauses. Once condensed, your 
substring code should begin working as before.

SELECT datecol +0 as  datecol
FROM ...

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Josh <josh2780@stripped> wrote on 03/13/2006 12:42:32 PM:

> Sure is...
> 
> SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField
> 
> Take a look at:
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
> 
> -Josh
> 
> --- Yesmin Patwary <yesmin25@stripped> wrote:
> 
> > Dear All,
> > 
> >   First of all, I would like to thank to Josh and
> > Peter Brawley for their kind help on previous issue.
> > 
> > 
> >   Here is another Issue:
> >   There are many tables that have timestamp field
> > with 8 char (yyyymmdd). I use this format all over
> > our website and to create various reports.  Below is
> > static php code that I used numerous places:
> >   <? 
> >    $year=substr("$date", 0, 4);
> >    $month=substr("$date", 4, 2);
> >    $day=substr("$date", 6, 2); 
> >   ?>
> > 
> >   All timestamp dependent reports and web pages had
> > problems, once I upgrade to MySQL – 4.1.12.  At the
> > end I had to downgrade to 3.23 to bring web site
> > operation to normal. Is there anyway to preserve old
> > timestamp format after upgrading?
> > 
> > MySQL - 3.23
> > +--------+------------+----------
> > |  Field |   Type     | Default (yyyymmdd)
> > +--------+------------+----------
> > |log_date|timestamp(8)| 00000000
> > 
> > MySQL - 4.1.12
> > +--------+-----------+--------------------
> > |  Field |   Type    | Default 
> > +--------+-----------+--------------------
> > |log_date| timestamp |0000-00-00 00:00:00
> > 
> > 
> > Josh <josh2780@stripped> wrote:
> >   You could rotate the output... basically get 1 row
> > with 11 columns
> > (CA01_count,CA02_count,...,CA12_count)
> > (leaving out CA10) joining all 12 tables together...
> > 
> > Or... perhaps we can help with the timestamp issues
> > you are having and get you upgraded to later version
> > of mysql that supports nested SELECT statements.
> > 
> > --- Yesmin Patwary wrote:
> > 
> > > Dear All,
> > > 
> > > I had some issues in past with timestamp fields as
> > a
> > > result I am unable to upgrade to mysql 4.1
> > version. 
> > > I am sure below the query recommended by Josh
> > works
> > > with 4.1 or above. Would it be possible to rewrite
> > > this query for 3.23 version? 
> > > 
> > > Again, thank you Josh and all others for your kind
> > > help and comments.
> > > 
> > > Josh wrote:
> > > Here's one method:
> > > 
> > > SELECT cl1.list_name, count(*) as count
> > > FROM customerList cl1
> > > WHERE cl1.id IN (SELECT cl2.id FROM customerList
> > cl2
> > > WHERE cl2.list_name='CA10')
> > > and cl1.list_name != 'CA10'
> > > GROUP BY cl1.list_name
> > > 
> > > --- Yesmin Patwary wrote:
> > > 
> > > > Good morning all,
> > > > 
> > > > We have 12 customer lists: CA01, CA02, ….,CA12. 
> > > > 
> > > > Table: customerList
> > > > +-----------+------+
> > > > | list_name | id |
> > > > +-----------+------+
> > > > | CA10 | 20BE |
> > > > | CA07 | 20BE |
> > > > | CA11 | 20BE |
> > > > | CA03 | 20BE |
> > > > | CA10 | NQCR |
> > > > | CA04 | NQCR |
> > > > | CA02 | MVYK |
> > > > | CA10 | 0BEC |
> > > > | …AND SO ON. |
> > > > +-----------+------+
> > > > 
> > > > Each list has 25 to 350 customers. Same
> > > > customer_id may exist in multiple lists. We need
> > > to
> > > > compare CA10 list customer_id’s with other 11
> > > lists
> > > > to find matching id count by list_name. The
> > query
> > > > output should be something similar below:
> > > > +------+-----------+
> > > > | list_name |count |
> > > > +------+-----------+
> > > > | CA05 | 60 |
> > > > | CA07 | 42 |
> > > > | CA01 | 35 |
> > > > | CA03 | 28 |
> > > > | CA09 | 15 |
> > > > | …AND SO ON… |
> > > > +-----------+------+
> > > > 
> > > > Can this be done with a SELECT statement without
> > > > using perl or php? 
> > > > 
> > > > Thanks in advance for any help.
> > > 
> > > 
> > > ---------------------------------
> > > Yahoo! Mail
> > > Bring photos to life! New PhotoMail makes sharing
> > a
> > > breeze. 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> > protection around 
> > http://mail.yahoo.com 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/mysql?unsub=yesmin25@stripped
> > 
> > 
> > 
> > 
> > ---------------------------------
> >  Yahoo! Mail
> >  Use Photomail to share photos without annoying
> attachments.
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1en@stripped
> 

Thread
Compare lists Query?Yesmin Patwary10 Mar
  • Re: Compare lists Query?Josh10 Mar
    • Re: Compare lists Query?Yesmin Patwary13 Mar
      • Re: Compare lists Query?Peter Brawley13 Mar
      • Re: Compare lists Query?Josh13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
          • Re: Problems with timestamp field after upgrading MySQL Server.Josh13 Mar
            • Re: Problems with timestamp field after upgrading MySQL Server.SGreen13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
        • INSERT...SELECT Query Help Request.Yesmin Patwary22 Mar
          • Re: INSERT...SELECT Query Help Request.SGreen22 Mar