How to convert machine readable date format to regular date format in mysql?

sunnyside · #1 (**permalink**) 04-11-07, 21:51

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?

dbmab · #2 (**permalink**) 04-11-07, 22:19

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.

sunnyside · #3 (**permalink**) 04-11-07, 22:48

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

aschk · #4 (**permalink**) 04-12-07, 04:23

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).

aschk · #5 (**permalink**) 04-12-07, 04:29

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)

aschk · #6 (**permalink**) 04-12-07, 04:31

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.