2

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

daria
  • 141
  • 2
  • 5
  • 17

4 Answers4

3

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
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
1

Drop the [ ] and move it to TIME type field and you should be fine

UPDATE tableName SET timeField = REPLACE(REPLACE(varCharField,'[',''),']','')

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • i've already omitted the '[' and set the datatype as time, but it didnt work. i forgot to tell you that the time value isn't limited to 0-23. i can have 56:00:00 value indicating it's 7 working days. – daria Aug 12 '10 at 13:27
  • What do you mean by 'didn't work'. MySQL's TIME column can hold values from '-838:59:59' to '838:59:59', so that shouldn't be an issue. – Mchl Aug 12 '10 at 13:41
  • it gave me the error: Bad format for Time '-05:48:49' in column 32 when i use a resultset.getTime(2) to get the value of that column. – daria Aug 16 '10 at 11:49
  • That seems to be an error in your application, not in MySQL. I don't know how your application handles time or what format it expects. – Mchl Aug 16 '10 at 12:39
1

Hye

This problem can be solved either setting time data in one column and a boolean flag in the other column of same record differ between '-' and non '-' records which I assume is a minus sign this is in general solution. As you are using mysql where  you can set the data directly with '-' sign which shows a negative value (time datatype) in column and as you said you need to compare them with conditions like 30 mins(00:30:00) etc you can use subtime() or addtime() mysql syntax for comparison purposes. Hope you find this answer appropriate.

regards

la_89ondevg

la89ondevg
  • 117
  • 1
  • 2
  • 10
0

I'm not an expert Java dev, but I know in php what your asking (if I understand it right) can be achieved with strtotime, so using that as a search, I found this thread.

PHP's strtotime() in Java

hopefully this helps.

Community
  • 1
  • 1
canadiancreed
  • 1,966
  • 6
  • 41
  • 58