Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    30

    Question Unanswered: How to convert machine readable date format to regular date format in mysql?

    There's a mysql table field (fld_date) which store date in format of "yyyy/mm/dd-hh:mm +0000".

    I'd like have a sql query which can group by "fld_date" (but I only want to group by the date (Y-m-d) only, don't need to specify the time)

    I know unix time stamp I can do something like:

    DATE_FORMAT(from_unixtime(fld_appointmentUnixTimes tamp), '%Y-%m-%d')

    Is there any way to do it from format like yyyy/mm/dd-hh:mm +0000?

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Is your fld_date an actual mysql DATETIME or TIMESTAMP type or is it a text/char type with those values stored in it?

    If it is an actual DATETIME/TIMESTAMP, you can use DATE(fld_date) to return or test only the date portion.

  3. #3
    Join Date
    Aug 2003
    Posts
    30
    Quote Originally Posted by dbmab
    Is your fld_date an actual mysql DATETIME or TIMESTAMP type or is it a text/char type with those values stored in it?

    If it is an actual DATETIME/TIMESTAMP, you can use DATE(fld_date) to return or test only the date portion.
    It's just a weird text type

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I think you may have difficulty if it's a text type and not a date type. Perhaps try modelling it as two fields fld_date and fld_date_offset where fld_date is a DATETIME (OR TIMESTAMP) and fld_date_offset is a SIGNED TINYINT.

    The other option if this is not available is to look at splitting up the fld_date text field using MySQL string functions, although i'm fairly positive there is no str_split function available (http://dev.mysql.com/doc/refman/5.0/...unctions.html).

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The functions you are most probably going to want to use are :
    LOCATE(substr,str), LOCATE(substr,str,pos)
    POSITION(substr IN str) is a synonym for LOCATE(substr,str)
    SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
    SUBSTRING_INDEX(str,delim,count)
    LENGTH(str)

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Incidently from_unixtime is not going to give you what you want as is requires an integer number :

    Code:
    FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
    
    Returns a representation of the unix_timestamp argument as a value 
    in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending
    on whether the function is used in a string or numeric context. The 
    value is expressed in the current time zone. unix_timestamp is an internal
    timestamp value such as is produced by the UNIX_TIMESTAMP() function.
    
    If format is given, the result is formatted according to the format string,
    which is used the same way as listed in the entry for the DATE_FORMAT()
    function.
    
    mysql> SELECT FROM_UNIXTIME(875996580);
            -> '1997-10-04 22:23:00'
    mysql> SELECT FROM_UNIXTIME(875996580) + 0;
            -> 19971004222300
    mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
        ->                      '%Y %D %M %h:%i:%s %x');
            -> '2003 6th August 06:22:58 2003'
    
    Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert
     between TIMESTAMP values and Unix timestamp values, the conversion
     is lossy because the mapping is not one-to-one in both directions. For
     details, see the description of the UNIX_TIMESTAMP() function.

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    please Note : All The Answers I Have Given Above Are Utilising Mysql5.0

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    ...although i'm fairly positive there is no str_split function available
    SUBSTRING_INDEX
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Yeh i mentioned that one in a further post...

  10. #10
    Join Date
    Aug 2003
    Posts
    30
    Thank you all. I will try to see if it works then tell you guys.

  11. #11
    Join Date
    Aug 2003
    Posts
    30
    Yes, I used SUBSTRING and it works great now

Posting Permissions

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