convert string to date and time

Posted on 2006-04-25
Last Modified: 2012-06-21
Hi -

Very new to perl - have searched the questions and can't find an answer I can understand :-)

I have two varchar fields in my mysql db that contain a date and a time as strings in the following formats:

date field looks like 04/25/06

time field looks like 9:45 AM

Question is -

How can I convert these text strings into actual dates and times that perl can work with - for date and time manipulations -

thanks in advance -

Question by:rwinnick
    LVL 11

    Expert Comment

    It depends on what module you use, I use Time::ParseDate
    the following code would print me 2006042521:45:00

    $d = "04/25/06";
    $t = "9:45 PM";
    print &ParseDate("$d $t");
    LVL 48

    Expert Comment

    What sort of date/time manipulations do you want to do?

    Author Comment

    Hi -
    I am trying to "fix" some pre existing perl code that I inherited which doesn't work :-)

    The problem goes like this :-)

    I have a date field which has a text string like 04/25/06
    I then have a time field which has a text string like 6:45 PM
    I need to take these date and time strings and turn them into a time stamp field using local server time (which is PST)
    I then need to adjust this timestamp to reflect the timezone the client is in - which is represented in another field as a one digit offset (0,1,2,3)

    Up till now I have tried to get the year, month, day, hour, min, and am/pm by using substr() and adjusting for string length 6:45 PM vs 10:45 AM

    I have then tried to use timelocal() to create the time stamp

    and then try to add 12 hrs if time is pm -

    I will then have to compare this timestamp to local server time and decide if an event should happen if the timestamp is less than local time -

    Am getting results but they are way off -

    I'm sure there has to be a more elegant and correct method of accomplishing this :-)

    But after more hours than I care to remember I stuck!

    thanks -


    Author Comment

    Hi Again -

    By the way I tried the time::parsedate - but get an error msg and it can't find the module

    LVL 84

    Accepted Solution

    $d = "04/25/06";
    $t = "9:45 PM";
    ($M,$D,$Y) = split'/',$d;
    ($h,$m,$p) = $t=~/(\d+):(\d+)\s*(PM)?/i;
    $h += 12 if $p;
    use Time::Local;
    $timestamp = timelocal(0,$m,$h,$D,$M-1,$Y+2000-1900);

    Author Comment

    Hi ozo -

    That is soooo cool and elegant - and it works!!

    Not only have you helped me with this problem - you've shown me a standard to work towards -

    thanks -


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    json encode mysql results 4 176
    Shell Script to find a string in server(s) log file 2 253
    stftime format 4 47
    Perl modules on linux ec2 3 77
    I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
    In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now