varchar java,如何在Java中将varchar转换为时间？

i'm copying data from a csv file to my database (mysql) with java. i've a time column where values can be h:mm:ss or - h:mm:ss (the - means that we surpassed a certain time delay).

so i was obliged to change the column type to varchar.

my problem now is i need to compare the value of records in this column, for example i need to show all records where the value of this column is under 30 min, knowing that the field value surpass the 24h format (can be 56:00:00).

thanks for your help

解决方案

Rather convert it to seconds and store it as a signed integer. You can't do numerical operations directly on strings/varchars, it would be a high cost of massaging it forth and back to the useable format. Then why not just store it directly in that format?

Here's a kickoff example how to convert your CSV field to seconds:

public static int toSeconds(String time) throws ParseException {

SimpleDateFormat positiveTime = new SimpleDateFormat("'['hh:mm:ss']'");

SimpleDateFormat negativeTime = new SimpleDateFormat("'[-'hh:mm:ss']'");

if (time.startsWith("[-")) {

return -1 * (int) negativeTime.parse(time).getTime() / 1000;

} else {

return (int) positiveTime.parse(time).getTime() / 1000;

}

}

Here's how you can use it and finally store it in DB:

String time1 = "[00:00:30]";

String time2 = "[- 00:10:20]";

int time1InSeconds = toSeconds(time1);

int time2InSeconds = toSeconds(time2);

// ...

preparedStatement = connection.prepareStatement("INSERT INTO tbl (col1, col2) VALUES (?, ?)");

preparedStatement.setInt(1, time1InSeconds);

preparedStatement.setInt(2, time2InSeconds);

To select times of over 30 seconds, just do like:

SELECT col1, col2 FROM tbl WHERE col1 > 30