SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: UDF problems

  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Canton, OH
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UDF problems

    This is my first UDF ever. My goal is to take a date in the form 'YYYY-MM-DD HH:MM AM' and turn it into a UNIX timestamp. I am migrating data from a table that stores dates like this in the mediumtext, to a table that stores the date in unix timestamps.

    Anybody have any idea why I keep getting:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

    Here's my UDF:

    DELIMITER $$

    CREATE FUNCTION CONVERT_PHP_TIMESTAMP(field MEDIUMTEXT)
    RETURNS INT(11)
    BEGIN
    DECLARE field_date VARCHAR(45);
    DECLARE field_hour VARCHAR(45);
    DECLARE field_minutes VARCHAR(45);
    DECLARE field_timestamp INT(11);

    IF SUBSTRING(field, -2) = 'AM' THEN
    SET field_timestamp = SUBSTRING(field, -2);
    ELSEIF SUBSTRING(field, -2) = 'PM' THEN
    SET field_date = SUBSTRING(field FROM 0 FOR 10);
    SET field_hour = SUBSTRING(field FROM 11 FOR 2);
    SET field_minutes = SUBSTRING(field FROM 14 FOR 2);
    SET field_hour = CONVERT(INT, field_hour);
    SET field_hour = (field_hour + 12);
    SET field_timestamp = CONCAT(field_date,' ', field_hour, ':', field_minutes);
    END IF;

    RETURN(SELECT unix_timestamp(field_timestamp));
    END
    $$


    DELIMITER ;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't think you have to do all that substring business, in fact i'm not sure a UDF is even needed

    use UNIX_TIMESTAMP(STR_TO_DATE(textfld,'%y-%m-%d %h:%i %p'))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Canton, OH
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for pointing me in the right direction. Your answer was close, but I needed to tweak it a bit to UNIX_TIMESTAMP(STR_TO_DATE(textfld,'%Y-%m-%d %h:%i %p')). Thanks for saving me from doing something much harder and unneeded.


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
  •