Specific Date Conversion Method Needed

[Michael D Sims]

Hello,

I’m trying to convert dates like this:
January 1, 2013 02:44 A.M.
March 13, 2013 11:35 P.M.

Into this:
2013-01-01 02:44:00
2013-03-13 23:35:00

So basically the end result is in this format: yyyy-MM-dd HH:mm:ss

I’m having a chore of a time finding the right way to do this ... here is the code I am using, but I get this error:
There was a problem with date formatting: Unparseable date: "January 1, 2013 02:44 A.M."
There was a problem with date formatting: Unparseable date: "March 13, 2013 11:35 P.M."

private void fixDates(String rawData)
{
	p = Pattern.compile("([a-zA-Z.]+ \\d{1,2}\\, )(\\d{1,4})(,)(\\s[\\d:]+)( A.M.| P.M.)");
	m = p.matcher(rawData);
	matchCount = p.split(rawData).length;
	counter = 0;
	sb = new StringBuffer();
	Date anewDate;
	while(m.find())
	{
	    String oldDate = m.group(1) + m.group(2) + m.group(4) + m.group(5);
	    DateFormat format = DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.SHORT);
    	try
	    {
    	    anewDate = format.parse(oldDate);
        	SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	        String finalDate = sDateFormat.format(anewDate);
    	    m.appendReplacement(sb,finalDate);
	    }
    	catch(Exception e)
	    {
    	    userMessage = userMessage + "There was a problem with date formatting: " + e.getMessage() + newLine;
	        updateMessage(userMessage);
    	}
	    counter++;
    	updateProgress(counter,matchCount);
	}
}

Any thoughts?

[Paul Clapham]

The error message you chose is slightly misleading, because the error is actually in parsing the text rather than in formatting a date. But anyway the date format you chose to parse the strings:

DateFormat format = DateFormat.getDateTimeInstance(DateFormat.MEDIUM,DateFormat.SHORT);

isn't consistent with the strings you're trying to parse. If you write some code which formats a date (e.g. now) and examine the output, you should see the difference.

So then you have two choices: you can manipulate your date strings to conform with that DateFormat object, or you can create a different DateFormat object which does match those strings.

[Michael D Sims]

Paul Clapham wrote:So then you have two choices: you can manipulate your date strings to conform with that DateFormat object, or you can create a different DateFormat object which does match those strings.

I’m thinking the first option is going to be less time consuming for me. The question is, how to determine the proper format for the DateFormat object?

[Michael D Sims]

Paul wrote: isn't consistent with the strings you're trying to parse. If you write some code which formats a date (e.g. now) and examine the output, you should see the difference.

So then you have two choices: you can manipulate your date strings to conform with that DateFormat object, or you can create a different DateFormat object which does match those strings.

I'm still having a heck of a time with this.

I tried both options and still I am not having any luck. Here is my latest attempt. I have verified that the result of the pattern matcher followed by the string I build on line 9 does in fact match the pattern I define in line 14, but line 15 still throws the un-parseable exception:

                    p = Pattern.compile("([a-zA-Z.]+ \\d{1,2})(\\,)( \\d{4})(, )(\\d{1,2})(:)(\\d{1,2})(AM|PM)");
                    m = p.matcher(rawData);
                    matchCount = p.split(rawData).length;
                    counter = 0;
                    sb = new StringBuffer();
                    String oldDateTime;
                    while(m.find())
                    {
                        oldDateTime = m.group(1) + m.group(3) + " " + m.group(5) + " " + m.group(7) + " " + m.group(8);
                        // This will yield a date time string like this: January 12 2013 2 45 PM

                        try
                        {
                            DateTimeFormatter fmt = DateTimeFormatter.ofPattern("M d Y h m a");
                            LocalDate ldt = LocalDate.parse(oldDateTime, fmt);
                            String aFinalDateTime = ldt.toString();
                            m.appendReplacement(sb, aFinalDateTime);
                        }
                        catch(Exception e)
                        {
                            userMessage = userMessage + "There was a problem with date formatting: " + e.getMessage() + newLine;
                            updateMessage(userMessage);
                        }
                        counter++;
                        updateProgress(counter,matchCount);
                    }
                    m.appendTail(sb);
                    rawData = String.valueOf(sb);

[Stefan Evans]

A couple of suggestions

1: When you post code, post a fully contained sample that can compiled . One that can be just copied/pasted and run. It just makes it easier for people to help you. Your code sample doesn't meet that criteria :-)
2: Give an indication of the input for your program. The only indication I can see is in the error message for unparsable date. Are the dates you are reading in that same format always, or can that change?
3: Possibly also break up your code into modules. One to find/retrieve date strings from the text. Another to convert that text into a date. That way you can develop and test them relatively independently.

My suggestion would be to use the java.text.SimpleDateFormat class to try and parse the input date.

SimpleDateFormat sdf = new SimpleDateFormat("MMMM dd, yyyy, hh:mm a");
Date resultDate = sdf.parse("January 1, 2013, 02:44 AM");
System.out.println(resultDate);

[Michael D Sims]

Stefan Evans wrote:1: When you post code, post a fully contained sample that can compiled.

This should compile and run just fine:

package com.company;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Main
{

    public static void main(String[] args)
    {
        String rawData = "January 11, 2015, 11:07PM\tPurchase Item\tmowf\t\t5\t1046\n" +
                  "January 11, 2015, 11:07PM\tPurchase Item\tmowf\t\t5\t1041\n" +
                  "September 17, 2014, 9:59PM\tPurchase Item\tdakotajt\t\t55\t1785\n" +
                  "September 17, 2014, 9:53PM\tPurchase Item\tcurioiusone\t\t55\t1730\n";
        Pattern p = Pattern.compile("([a-zA-Z.]+ \\d{1,2})(\\,)( \\d{4})(, )(\\d{1,2})(:)(\\d{1,2})(AM|PM)");
        Matcher m = p.matcher(rawData);
        StringBuffer sb = new StringBuffer();
        String oldDateTime;
        while(m.find())
        {
            oldDateTime = m.group(1) + m.group(3) + " " + m.group(5) + " " + m.group(7) + " " + m.group(8);
            // This will yield a date time string like this: January 12 2013 2 45 PM

            try
            {
                DateTimeFormatter fmt = DateTimeFormatter.ofPattern("M d Y h m a");
                LocalDate ldt = LocalDate.parse(oldDateTime, fmt);
                String aFinalDateTime = ldt.toString();
                m.appendReplacement(sb, aFinalDateTime);
            }
            catch(Exception e)
            {
                System.out.println("There was a problem with date formatting: " + e.getMessage());
            }
        }

        m.appendTail(sb);
        rawData = String.valueOf(sb);
        System.out.println(rawData);
    }
}

Stefan Evans wrote:2: Give an indication of the input for your program.

The rawData variable as declared in my code is a sufficient example of what I'm using for input, only picture that string with over 80,000 lines of text that are IDENTICAL in format.

Stefan Evans wrote:3: Possibly also break up your code into modules.

This excerpt is an excerpt of such a module ... in fact, it runs in a thread that processes a string read in from a text file applying close to 20 Regex Pattern matches with modification similar to the code posted above.

Stefan Evans wrote:My suggestion would be to use the java.text.SimpleDateFormat class to try and parse the input date.

SimpleDateFormat sdf = new SimpleDateFormat("MMMM dd, yyyy, hh:mm a");
Date resultDate = sdf.parse("January 1, 2013, 02:44 AM");
System.out.println(resultDate);

Let me give this a whirl ...

OK, I experimented with what you posted here, but it still did not come out in the format that I was seeking ... so with some experimentation using what you posted as a kick-off for the experimentation, I came up with this, and in the end, I get exactly what I need, although not the way I was hoping to obtain it since I have to next a pattern matcher in a pattern matcher.

package com.company;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Main
{

    public static void main(String[] args)
    {
        String rawData = "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1046\n" +
                  "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1041\n" +
                  "September 17, 2014, 09:59 PM\tPurchase Item\tdakotajt\t\t55\t1785\n" +
                  "September 17, 2014, 09:53 PM\tPurchase Item\tcurioiusone\t\t55\t1730\n";
        Pattern p = Pattern.compile("([a-zA-Z.]+ \\d{1,2})(\\,)( \\d{4})(, )(\\d{1,2})(:)(\\d{1,2} )(AM|PM)");
        Matcher m = p.matcher(rawData);
        StringBuffer sb = new StringBuffer();
        String oldDateTime;
        String finalDateTime = "";
        String pattern = "MMMM dd, yyyy, hh:mm a";
        while(m.find())
        {
            oldDateTime = m.group(0);

            try
            {
                DateTimeFormatter fmt = DateTimeFormatter.ofPattern(pattern);
                LocalDateTime ldt = LocalDateTime.parse(oldDateTime, fmt);
                String intDateTime = ldt.toString();
                Pattern ptn = Pattern.compile("(\\d{4}-\\d{2}-\\d{2})(T)(\\d{2}:\\d{2})");
                Matcher mtc = ptn.matcher(intDateTime);
                while(mtc.find())
                {
                    finalDateTime = mtc.group(1) + " " + mtc.group(3) + ":00";
                }
                m.appendReplacement(sb, finalDateTime);
            }
            catch(Exception e)
            {
                System.out.println("There was a problem with date formatting: " + e.getMessage());
            }
        }

        m.appendTail(sb);
        rawData = String.valueOf(sb);
        System.out.println(rawData);
    }
}

Here is the output

2015-01-11 23:07:00	Purchase Item	mowf		5	1046
2015-01-11 23:07:00	Purchase Item	mowf		5	1041
2014-09-17 21:59:00	Purchase Item	dakotajt		55	1785
2014-09-17 21:53:00	Purchase Item	curioiusone		55	1730

Now, the Date/Time field will be able to be inserted into a mySQL table...

[Carey Brown]

Here's my stab at your problem.

public class DateTimeStuff
{
	public static void main( String[] args )
	{
		DateTimeFormatter	inputFormatter = DateTimeFormatter.ofPattern( "MMMM dd, yyyy, hh:mm a" );
		DateTimeFormatter	outputFormatter = DateTimeFormatter.ofPattern( "yyyy-MM-dd HH:mm:00" );
		String rawData = "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1046\n"
				+ "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1041\n"
				+ "September 17, 2014, 09:59 PM\tPurchase Item\tdakotajt\t\t55\t1785\n"
				+ "September 17, 2014, 09:53 PM\tPurchase Item\tcurioiusone\t\t55\t1730\n";
		
		String[]	lines = rawData.split( "\\n" );
		for( String line : lines )
		{
			String[]	columns = line.split( "\\t" );
			String		ldt = LocalDateTime.parse( columns[0], inputFormatter )
				.format( outputFormatter );
			System.out.format( "%s\t%s\t%s\t%s\t%s\t%s\n", ldt,
				columns[1], columns[2], columns[3], columns[4], columns[5] );
		}
	}
}

[Stefan Evans]

Ok just to point this out: Carey has spotted that you have a tab separated file that you're reading in with a predictable format.
So instead of using a pattern matcher to find dates, read the file line by line, and assume that the first entry on each line is the date you are after.
You could even go a step further and define a java object to represent each line. Once you have the data out of String form, and in memory, manipulating becomes a lot easier.

>Now, the Date/Time field will be able to be inserted into a mySQL table...
Once you have a Date object you should be able to insert that directly into the database via JDBC, rather than formatting it into a String.
I find it preferable to deal with Date objects rather than keep reformatting it to what ever string format the database accepts.

[Michael D Sims]

Stefan Evans wrote:Ok just to point this out: Carey has spotted that you have a tab separated file that you're reading in with a predictable format.

Actually, by the time I'm to the point where I need to format the date so that I can build a SQL Insert statement, I have already massaged the data to get it to where I can do anything with it.

The raw data as it is initially read into a string variable has dates that vary IMMENSELY. For example, Here are the various ways that a date / time is given in the raw data:

January 1, 2013, 2:30 p.m.
January 1, 2013, noon
January 1, 2013, midnight
Jan. 1, 2013, noon
Jan. 1, 2013, midnight
Jan. 1, 2013, 2 p.m.
Jan. 1, 2013, 4 a.m.

It takes several regex find and replace runs to get the dates in the format I started this thread with. I just didn't want to complicate the issue any more than was necessary.

Also, the input file breaks each "record" up with sometimes three different line feeds, so I have to Regex out the line feeds, replace them with a unique pattern, then search for what should be a Regex pattern indicating the beginning of each line, then insert line feeds before that pattern, then use my unique replacement pattern ... and replace it with tabs to maintain the tab delimited nature of the data - as it is tab delimited, but not always as sometimes its line feed delimited ... it's very archaic in the raw data.

Stefan Evans wrote:You could even go a step further and define a java object to represent each line. Once you have the data out of String form, and in memory, manipulating becomes a lot easier.

I'd be interested in seeing code example for this. I'm thinking once I get the data into consistent form, then getting it into objects then using JDBC to get it into the database might be more effecient than building strings into SQL statements etc.

[Carey Brown]

Eee gads! What a mess you've inherited. This might be a good time to implement test-driven-development and write a slew of JUnit tests for as many possible permutations as you can come up with. That will be the only way I can see to have any confidence that you've got the bases covered. Only then would I tackle regular expressions, state machines, or whatever.

P.S. I hate "midnight", it is very ambiguous as far as human interpretation goes. Midnight == 00:00:00 am == first instant of the new day.

[Michael D Sims]

Carey Brown wrote:Here's my stab at your problem.

public class DateTimeStuff
{
	public static void main( String[] args )
	{
		DateTimeFormatter	inputFormatter = DateTimeFormatter.ofPattern( "MMMM dd, yyyy, hh:mm a" );
		DateTimeFormatter	outputFormatter = DateTimeFormatter.ofPattern( "yyyy-MM-dd HH:mm:00" );
		String rawData = "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1046\n"
				+ "January 11, 2015, 11:07 PM\tPurchase Item\tmowf\t\t5\t1041\n"
				+ "September 17, 2014, 09:59 PM\tPurchase Item\tdakotajt\t\t55\t1785\n"
				+ "September 17, 2014, 09:53 PM\tPurchase Item\tcurioiusone\t\t55\t1730\n";
		
		String[]	lines = rawData.split( "\\n" );
		for( String line : lines )
		{
			String[]	columns = line.split( "\\t" );
			String		ldt = LocalDateTime.parse( columns[0], inputFormatter )
				.format( outputFormatter );
			System.out.format( "%s\t%s\t%s\t%s\t%s\t%s\n", ldt,
				columns[1], columns[2], columns[3], columns[4], columns[5] );
		}
	}
}

Boy, I REALLY like this ... I mean, I REALLY LIKE THIS ... my problem is, I am not educated enough in Java to know about some of these cool tricks that you've used here. For example, the way that you have read in the data using an input formatter and simultaneously wrote it back out to the string in one line via the LocalDateTime.parse method is something I didn't even know was possible. I use IntelliJ to write code with, and when I type in LocalDateTime.parse and the context sensitive help pops up with all of the various uses for parse, nothing in that help speaks to me the ability to do string, inputFormatter).format(outputFormatter) ... so how is a novice supposed to figure this stuff out on his own?

Also, I didn't know that System.out had a way to format output like that. I'm assuming that each %s is a place holder which is replaced with each column index in a sequential one for one match ... that is quite nice!

[Michael D Sims]

Carey Brown wrote:Eee gads! What a mess you've inherited.

Funny you say that, because at the end of the thread I made to massage all that data, I post this to the user inside a TextArea (bound to the updateMessage method in the thread):

Done with RegEx data scrub. BOY WHAT A MESS!
Click the Finish Processing button to post data to database.

hehe :-)

Carey Brown wrote:This might be a good time to implement test-driven-development and write a slew of JUnit tests for as many possible permutations as you can come up with. That will be the only way I can see to have any confidence that you've got the bases covered. Only then would I tackle regular expressions, state machines, or whatever.

Can you elaborate on this for me? "JUnit tests"??

Carey Brown wrote:P.S. I hate "midnight", it is very ambiguous as far as human interpretation goes. Midnight == 00:00:00 am == first instant of the new day.

Well, my audience is North American, so 12:00 AM for midnight and 12:00 PM for noon seemed proper to me. But yes, when I actually discovered the use of noon and midnight in the data, you bet anyone within ear shot of me heard enough expletives to fill a weekend. This data actually came from a mySQL database by the way - and no, I do not have any way at all to get the data straight from their database ... I was told to go pound sand when I asked - the report for the data output was obviously created by someone with a sense of humor.

[Carey Brown]

Michael D Sims wrote:For example, the way that you have read in the data using an input formatter and simultaneously wrote it back out to the string in one line via the LocalDateTime.parse method is something I didn't even know was possible. I use IntelliJ to write code with, and when I type in LocalDateTime.parse and the context sensitive help pops up with all of the various uses for parse, nothing in that help speaks to me the ability to do string, inputFormatter).format(outputFormatter) ... so how is a novice supposed to figure this stuff out on his own?

Well you can write it out long hand first like this:

LocalDateTime	localDateTime = LocalDateTime.parse( column[0], inputFormatter );
String		    ldt = localDateTime.format( outputFormatter );

Then notice that the output of parse() is a LocalDateTime object that then gets used to invoke format(). Then just eliminate that temporary variable and paste them together. Works for all object method calls. It will also work with several layers of method calls:

Type var = obj.methodA().methodB().methodC(); // etc

So, you just have to pay attention to the type that is returned from each method.

[Carey Brown]

Michael D Sims wrote:

Carey Brown wrote:This might be a good time to implement test-driven-development and write a slew of JUnit tests for as many possible permutations as you can come up with. That will be the only way I can see to have any confidence that you've got the bases covered. Only then would I tackle regular expressions, state machines, or whatever.

Can you elaborate on this for me? "JUnit tests"??

JUnit is a unit testing framework for the Java programming language. See junit.org. I don't mean to side track you with this, essentially you need to be able to create a whole slew of different input data scenarios, run them through your business logic and compare the results against the results that you expected. JUnit is just a tool to do this but it has its own learning curve. In test-driven-development the idea is to write the tests first, which of course, will fail. And one by one add the necessary additions to your business logic until all test cases pass.

[Michael D Sims]

Carey Brown wrote:

Michael D Sims wrote:

Carey Brown wrote:This might be a good time to implement test-driven-development and write a slew of JUnit tests

Can you elaborate on this for me? "JUnit tests"??

In test-driven-development the idea is to write the tests first, which of course, will fail. And one by one add the necessary additions to your business logic until all test cases pass.

OK, I understand now. I do something similar when I build my RegEx searches. I use a program called RegExRx that lets me paste some sample data, then I can work on my RegEx and as I'm typing out my RegEx, it highlights for me, in real time, the text that my code hits on. Then, I can click on a replace tab, and it opens an empty text area where I start typing in replacement Strings and it shows me what the replaced text will look like (also in real time). However, it isn't as fancy as being able to build multiple different query's simultaneously.

I looked at junit.org briefly, and you're assertion is correct, I don't think at this point, consuming the learning curve would be in my best interest since I have already completed all of my RegEx code with acceptable results. Thank you for the information though, as I'm sure this will come in handy for me sometime in the future.

I also wanted to comment on something that I found very interesting. When I used your method of converting the date formats on the large data set I was working with, it took over two minutes to completely run through 70,000 lines of text which all had dates that needed formatting. I ran the code at least twenty different times, the last five or so runs, I measured the actual time it took using System.currentTimeMillis() before then after the code section, showing the difference via System.out.println(). So I built a different method using RegEx search and replace functions, which included a separate method I created where I pass the isolated hour String to the method, which then returns the hour in 24-hour format. When I ran the code, I was astounded when it only took less than two seconds to process 70,000 lines of text.

So I was going to come back here and share my findings, but I decided I should build a new project that you could compile and examine for yourself. When I did this, I discovered that both ways of doing it took literally less than 3 seconds. Your method still took twice as long as mine, but in the scope of three seconds, it became a moot point. But that begged the question, WHY? So what I did then, was delete BOTH portions of the date formatting code from my main project, and I replaced them with BOTH methods that I built in the new project (with only minor changes), and I was surprised to see that again, either method took less than three seconds. So I'm not sure why your code took so long the first time I implemented it, and I didn't want to take the time to revert my code back to where it was when it took a long time because it seemed irrelevant at that point to consume more time on this. But as a side note, this code does run inside of a JavaFX concurrent thread. I'm not sure if that has anything to do with why it was so slow the first time I coded it, but for whatever reason, simply re-doing the code made a HUGE difference in processing time. And the weird thing is, I could not really see where I was doing anything differently in my new code so I don't know how to explain my results. But as the adage goes, "Don't look a gift horse in the mouth" ... or maybe in context of this site, we should say, "Don't look a gift cow in the mouth".



Just in case you are interested, here is what I ended up using to achieve the results I needed. I stuck with my RegEx code, since first of all, it was twice as fast as your method, and second, it conformed to the rest of my code in terms of how I manipulate the data. I felt that keeping a consistency in the code would ultimately be best:

                    userMessage = userMessage + "Formatting dates for mySQL DateTime format" + newLine;
                    updateMessage(userMessage);
                    String month = null, hh, mm, a, find, year, day, rest;
                    rawData = rawData + "\n";
                    p = Pattern.compile("([a-zA-Z]+)( )(\\d{2})(, )(\\d{4})(, )(\\d{2})(:)(\\d{2})( )(AM|PM)(.+\n)");
                    m = p.matcher(rawData);
                    matchCount = p.split(rawData).length;
                    counter = 0;
                    sb = new StringBuffer();
                    while(m.find())
                    {
                        hh = m.group(7);
                        mm = m.group(9);
                        a = m.group(11);
                        find = m.group(1);
                        year = m.group(5);
                        day = m.group(3);
                        rest = m.group(12);
                        switch (find)
                        {
                            case "January":
                                month = "01";
                                break;
                            case "February":
                                month = "02";
                                break;
                            case "March":
                                month = "03";
                                break;
                            case "April":
                                month = "04";
                                break;
                            case "May":
                                month = "05";
                                break;
                            case "June":
                                month = "06";
                                break;
                            case "July":
                                month = "07";
                                break;
                            case "August":
                                month = "08";
                                break;
                            case "September":
                                month = "09";
                                break;
                            case "October":
                                month = "10";
                                break;
                            case "November":
                                month = "11";
                                break;
                            case "December":
                                month = "12";
                                break;
                        }
                        sR = year + "-" + month + "-" + day + " " + fixHour(hh,a) + ":" + mm + ":00" + rest.replaceAll("\\$","\\\\\\$");
                        m.appendReplacement(sb,sR);
                        counter++;
                        updateProgress(counter,matchCount);
                    }
                    m.appendTail(sb);
                    rawData = String.valueOf(sb);

The reason I added rest.replaceAll("\\$","\\\\\\$") at the end of the final String is because I discovered that when I use StringBuilder for these search / replace methods, if my Strings have dollar signs in them, Java freaks out, so I have to format the dollar signs like this and they end up coming back into the final String perfectly.

And here is the fixHour method I created ... notice the archaic way of figuring out how to state the hour in 24-hour format based on whether or not the source String is AM or PM ... I'm rather proud of my Neanderthal ways:

                private String fixHour(String hh,String a)
                {
                    int iH = Integer.valueOf(hh);
                    if(a.equals("PM") && iH < 12)
                    {
                        return String.valueOf(iH + 12);
                    }
                    if(a.equals("AM") && iH == 12)
                    {
                        return "00";
                    }
                    return hh;
                }

Notice it will ALWAYS return a two digit String (hh is pre-formatted as two digits before being passed into this method), which is critical in the final MySQL format, because mySQL is a picky little bugger.

I find that doing this stuff is VERY fun ... I may need help.