List:General Discussion« Previous MessageNext Message »
From:Josh Date:March 13 2006 5:42pm
Subject:Re: Problems with timestamp field after upgrading MySQL Server.
View as plain text  
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=1
> 
> 
> 
> 			
> ---------------------------------
>  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 
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