SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: date problem

  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up date problem

    hi
    i am storing the dates in my database as string of 10 digits means in seconds from the function of strtotime();
    now i wan to know that when i m selecting the dates from the database i wan to pick only days like 02-05-2005,03-05-2005,04-05-05......etc.but they should only come once coz when i am seelcting them from "select date form...." it just give me dates liek this but repeating.
    its urgent plzzz.....
    regards
    phphelp

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if I understood your question right, but if you want to select unique dates you could use
    SELECT DISTINCT(date) ....
    If I misunderstood you, be more specific

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually i tell u wht i am doing actually

    i am running this query

    "select FROM_UNIXTIME(date, '%Y-%m-%d') AS formatted_date from table1 GROUP BY formatted_date order by formatted_date desc";

    but i want to do it now this way

    "select FROM_UNIXTIME(date+28800, '%Y-%m-%d') AS formatted_date from table1 GROUP BY formatted_date order by formatted_date desc";

    the first query is running properly but the second one is giving no result.one point more both queries run properly on my localhost but the second not working online in my site.any problem in query or thier can be any otehr way alos to do it same coz i want onlt the days (e.g 02-05-05) to know how mant messages sent every day.i hope u understand now.....
    its urgent plzzz.....
    regards
    phphelp

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I assume you are selecting other columns as well, otherwise you don't need the group by but a distinct select.
    Anyway, what version are you running both on your localhost and your server?
    What column type have you assigned for the timestamp?

  5. #5
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i m using version 4.0* but on my server it version 4.0*.
    timestamp i don't understand dear. how can i sue it then.i am using group by to group the dates....
    its urgent plzzz.....
    regards
    phphelp

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you just want to retrieve the date you do not need to GROUP BY
    Use
    SELECT DISTINCT(FROM_UNIXTIME(date+28800, '%Y-%m-%d')) from table1
    IF your query retrieves additional columns post the complete query

  7. #7
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no i am retrieving the distinct records teh only problem is it i snot working on my server when i am doing "FROM_UNIXTIME(date+28800, '%Y-%m-%d')) " but working with "FROM_UNIXTIME(date, '%Y-%m-%d')) ". any other solution?/??
    its urgent plzzz.....
    regards
    phphelp

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by phphelp
    no i am retrieving the distinct records teh only problem is it i snot working on my server when i am doing "FROM_UNIXTIME(date+28800, '%Y-%m-%d')) " but working with "FROM_UNIXTIME(date, '%Y-%m-%d')) ". any other solution?/??
    What do you mean it is not working?
    You get an error,etc?
    Additionally date+28800 will always return the same date.
    Why do you need it?

  9. #9
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually no error is coming.it just not returning any value means no row.and im doing "date+28800" to show the real date of my country coz it has a difference of 8 hours so want to make the date according to my country coz when saving it is saving form the server date.
    its urgent plzzz.....
    regards
    phphelp

  10. #10
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    bahrain
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    one thing more i found this function also "DATE_FORMAT(DATE_ADD(date, INTERVAL 8 HOUR), '%Y-%m-%d')" but i have date in seconds like '1192587456' so can it work with it?????? coz it i using date like this '1999-02-03'????
    its urgent plzzz.....
    regards
    phphelp


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •