Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Specific Date Conversion Method Needed

 
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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."



Any thoughts?
 
Marshal
Posts: 25682
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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:

 
Bartender
Posts: 1845
10
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Michael D Sims
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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



This should compile and run just fine:



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.


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.



Here is the output

Now, the Date/Time field will be able to be inserted into a mySQL table...
 
Bartender
Posts: 7208
65
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's my stab at your problem.
 
Stefan Evans
Bartender
Posts: 1845
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Bartender
Posts: 7208
65
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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


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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Bartender
Posts: 7208
65
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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:

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:

So, you just have to pay attention to the type that is returned from each method.
 
Carey Brown
Bartender
Posts: 7208
65
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 167
1
IntelliJ IDE MySQL Database Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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:


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:




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.

 
The moth suit and wings road is much more exciting than taxes. Or this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic