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 functions you are most probably going to want to use are :
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)
Incidently from_unixtime is not going to give you what you want as is requires an integer number :
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()
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
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.