SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: Unix_timestamp

  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Hawai'i
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unix_timestamp

    Hey,

    Im working on displaying the date on page using a a SQL Query. Im not sure how to get it to print the date in this order month/day/year.

    I set it up in phpmyadmin to display the date but it seems like its doing it in reverse. Someone told me to put UNIX_TIMESTAMP but it gives me an error please help.

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Which column type are you using?
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Hawai'i
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im sorry what?

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is the column you have your date info in actally date type or is it char type?
    If it is of date type then you can look up the use of DATE_FORMAT in the manual to give you display options. If your date info is in a char column type you would have to convert the date.

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a similar problem I hope you dont' mind that I'm using this thread to my problem.
    I'm using PHP insert forms which use the US dateformat when they are submitted and I'm trying to convert the dates to european dateformat. (Unix_timestamp) Is there another way to do this than convert the code on each PHP page?.. or is it possible to change the database to using the american dateformat?..anyway I can't get neither to work and showing my PHP code here is probably wrong forum, so but I'm still wonder if it's possible to change MySQL to use US dateformat.
    I'm greatful for any comments.
    cheers
    /rz

  6. #6
    SitePoint Wizard Crowe's Avatar
    Join Date
    Nov 2001
    Location
    Huntsville
    Posts
    1,117
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I never store date formats in any country format. I store them in unix timestamp (epoch time) and then convert them on the fly to any date format I need. unix timestamp is the number of seconds since 00:00:00 GMT, January 1, 1970.

    MySQL has a date colum that is in its own format. It sounds like your storing a string formatted for us time format. You can use strtotime() in php to convert the string to unix timestamp and then php can format it anyway you want, like so:

    PHP Code:
    $formatted_date date("m/d/Y"$unformatted_date); 
    Your date would then look like 2/10/2005 if the $unformatted_date was for today. You can look ate the date() function for more formatting options.

    Hope that helps.
    Chrispian H. Burks
    Nothing To Say

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Crowe
    I never store date formats in any country format.
    that's because you can't anyway

    mysql stores dates in its own internal format, which happens to be 3 bytes -- yes, 3 bytes, which is why it's wrong to suggest that it's stored in this format or that format or any particular format

    microsoft access and sql server, by the way, use whole integer numbers to store dates and times, one number for the date and one for the time -- they are similar to unix time but based on a different zero date

    one thing to take into consideration is whether you would need to do a conversion on a stored database date or datetime value in order to do a search

    if you wanted to find all rows which had an effective date of February, and the dates were all stored in unix times, you have to be very careful never to apply a conversion function to the database table column, or else guess what -- inefficient table scan!!

    you need to make sure that your WHERE clauses have just the "naked" column on one side of the condition

    bad --> where month(from_unixtime(mycolumn)) = 02

    good --> where mycolumn between 1107241200 and 1109660399

    otherwise, the database engine can't use the index on the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Crowe,
    Believe me I've tried all different solutions the last two days whitout any luck including strtotime function. I tried your code now it didn't work..am I missing something??

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow r937,
    thanks for your lesson. so what's the best datatype to store date values in, when I'm using select and insert pages driven by PHP in US dateformat?..wow.. did you got that?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by far the best format to store dates in is DATE

    to insert date values, you have a choice of format --

    - a string 'YYYY-MM-DD '
    - a string 'YY-MM-DD'
    - a string 'YYYYMMDD'
    - a string 'YYMMDD'
    - a number YYYYMMDD
    - a number YYMMDD

    to display a date, you can either do the formatting in your scripting language, or else you can let mysql format it for you into a string using the DATE_FORMAT function

    i recommend reading up on all of the mysql date functions -- some of them are fantastic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your lessons on this r937,
    That really helped and maked that part more clear to me. It still leave me with my original problem though, to convert the submission form from US dateformat to european format. I'll continue my struggle.
    /rz

  12. #12
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    input the date in yyyy-mm-dd format as told by Rudy, then check the fourth item in this thread where I suggested you look up DATE_FORMAT in the manual.

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    san diego
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay,
    I think this needs an explanation. You see I have no problem to save the data in european format. and believe me I have look up and tried the date_format function.
    when I submit my update form, it register todays date even if I put a date from last month..regardless what I type in it only register todays date. Is that what the date format function supposed to do?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, the DATE_FORMAT function is to choose the way todisplay (output) your dates

    to insert (input) date values, you have only these choices of format --

    - a string 'YYYY-MM-DD '
    - a string 'YY-MM-DD'
    - a string 'YYYYMMDD'
    - a string 'YYMMDD'
    - a number YYYYMMDD
    - a number YYMMDD
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •