如何在Java中将varchar转换为时间? [英] how to convert a varchar to time in java?

问题描述

我正在使用Java将csv文件中的数据复制到我的数据库(mysql).我有一个时间列，其值可以为h:mm:ss或- h:mm:ss(-表示我们超过了一定的时间延迟).

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).

所以我不得不将列类型更改为varchar.

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

我现在的问题是我需要比较此列中记录的值，例如，我需要显示所有列的值在30分钟以下的记录，因为知道字段值超过了24h格式(可以是56:00:00).

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).

感谢您的帮助

推荐答案

而是将其转换为秒并将其存储为有符号整数.您不能直接在字符串/varchars上执行数字运算，将其推回并返回可用格式的成本很高.那为什么不直接以这种格式存储它呢?

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?

这是一个启动示例，说明如何将CSV字段转换为秒:

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;
    }
}

这里是如何使用它并将其最终存储在DB中的方法:

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);

要选择30秒以上的时间，只需执行以下操作:

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

SELECT col1, col2 FROM tbl WHERE col1 > 30

这篇关于如何在Java中将varchar转换为时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！