MySQL datetime의 시간 반올림

 

 

 

 

 

최근 데이터 생성 api를 호출하고 조회 api를 호출하였을때, false로 반환되어야 하는 반환값이 true로 반환되고 있는 문제가 발생했다. 

 

 

 

 

 

 

문제상황

해당 반환값은 LocalDateTime의 데이터를 주어진 시점(LocalDateTime)과 비교하여, 현재시점 이후라면, true를 이전이라면 false를 반환하도록 로직이 구현되어있었다. 예시 코드는 다음과 같다. 

@Entity
class Test (
    @Column(columnDefinition = "datetime(6)")
    val time: LocalDateTime
) : BaseEntity() {
    fun isAfter() = time.isAfter(LocalDateTime.now())
}

 

분명 데이터를 생성하는 시점에 LocalDateTime.now()로 현재시점을 부여하여 저장했고, 생성 api를 호출한 이후에 조회 api를 호출했기 때문에 반환값은 시간 순서상 생성 시점보다 이후이기 때문에 해당 값은 false (이전인 값)로 반환되기를 기대했다. 

그런데 실제로는 계속해서 true로 반환이 되었다. 

 

왜때문에...?!?

 

 

 

 

 

문제 원인 

시간 비교 로직에서 문제가 발생하는 것으로 파악이 되어 DB 데이터 타입을 확인해보니 datetime...! 

시간 데이터를 확인해보니, 데이터는 초단위까지만 기록이 되고 있었다. 검색해서 찾아보니, 세상에...! MySQL의 datetime의 경우 반올림이 발생한다는 것을 찾을 수 있었다....!

 

 

 

실제인지 테스트 코드로 만들어보자. 

인위적으로 반올림이 될 수 있는 시간대로 설정하여 시간엔티티를 저장하도록 했고, 해당 테스트가 실패하는 것을 확인할 수 있었다. 

@Entity
class Test(
    @Column(columnDefinition = "datetime")
    val time: LocalDateTime
) : BaseEntity() {
    fun isAfter(time: LocalDateTime) = this.time.isAfter(time)
}

@SpringBootTest
class DatetimeTest {


    @Autowired
    private lateinit var testRepository: TestRepository


    @Test
    @Rollback(false)
    fun test() {
        // 2024년 June 5일 Wednesday PM 1:28:50.783
        val beforeInstant = Instant.ofEpochMilli(1717594130783)
        val time = beforeInstant.atZone(ZoneOffset.UTC).toLocalDateTime()

        // 2024년 June 5일 Wednesday PM 1:28:50.883
        val afterInstant = Instant.ofEpochMilli(1717594130883)

        val test = testRepository.findById(testRepository.save(Test(time = time)).id).get()
        // 2024-06-05T13:28:51
        println(test.time)
        // 테스트 케이스 실패 => before 시간이 after 시간보다 이후인 것으로 판단이 됨
        assertFalse(test.isAfter(afterInstant.atZone(ZoneOffset.UTC).toLocalDateTime()))
    }
}

 

 

데이터베이스에는 어떻게 저장되었을까? 

반올림 된 시간으로 저장됨

데이터 베이스를 확인해보니, 2024-06-05 13:28:50.783인 시간대가 반올림되어 2024-06-05 13:28:51로 저장되는 것을 확인할 수 있었다...! 

 

 

 

 

해결 

해당 케이스를 해결하기 위해서 데이터 베이스에 좀더 정확한 값을 저장하는 방식을 찾아보았고, Fractional Seconds를 이용하여 간단하게 DB설정을 통해 문제를 해결할 수 있었다. 

참고 ) https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

MySQL has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, 
with up to microseconds (6 digits) precision

 

fractional seconds란 시간 정확도를 높이기 위한 초 이하 단위 (ms 부터의 시간)을 뜻하며 MySQL 5.6부터 6자리의 범위(마이크로 초)까지 지원된다. column definition의 뒷자리에 fractional seconds숫자를 지정하여 다음과 같이 사용할 수 있다. 

 

@Entity
class Test(
    @Column(columnDefinition = "datetime(6)")
    val time: LocalDateTime
) : BaseEntity() {
    fun isAfter(time: LocalDateTime) = this.time.isAfter(time)
}

 

 

이제 테스트를 다시 실행해보면, 데이터베이스에 저장되는 시간 정확도가 증가되어 테스트 케이스가 성공하는 것을 확인할 수 있다. 

@SpringBootTest
class DatetimeTest {


    @Autowired
    private lateinit var testRepository: TestRepository


    @Test
    @Rollback(false)
    fun test() {
        // 2024년 June 5일 Wednesday PM 1:28:50.783
        val beforeInstant = Instant.ofEpochMilli(1717594130783)
        val time = beforeInstant.atZone(ZoneOffset.UTC).toLocalDateTime()

        // 2024년 June 5일 Wednesday PM 1:28:50.883
        val afterInstant = Instant.ofEpochMilli(1717594130883)

        val test = testRepository.findById(testRepository.save(Test(time = time)).id).get()
        // 2024-06-05T13:28:50.783
        println(test.time)
        // 테스트 케이스 성공 => before 시간이 after 시간보다 이전인 것으로 판단이 됨
        assertFalse(test.isAfter(afterInstant.atZone(ZoneOffset.UTC).toLocalDateTime()))
    }
}

 

 

데이터베이스에도 정확한 시간대가 포함되는 것을 확인할 수 있다. 

 

 

 

 

추가문제

 

해당 데이터의 데이터 타입을 datetime(6)으로 업데이트 쿼리를 DB 팀에게 요청 드렸을 때, 추가적인 피드백을 받을 수 있었는데, 바로 데이터를 넣을때 fractional seconds가 정해진 데이터 타입의 범위(6자리)보다 더 넓은 범위라면 동일하게 반올림 되어 동일한 문제가 발생할 수 있다는 것이었다! 

 

 

이를 테스트 하기 위해서 LocalTime.Max()를 이용하여 테스트 해보자. 

LocalTime.Max()의 경우 fractional seconds를 9자리 만큼 가지고 있고, 9자리는 MySQL이 최대로 지원하는 6자리를 초과하기 때문에 결국 다음날로 저장이 되게 된다. 

    @Test
    @Rollback(false)
    fun test2() {
        // 2024년 June 5일 Wednesday PM 11:59:59.999999999
        val beforeTime = LocalDate.of(2024, 6, 5).atTime(LocalTime.MAX)

        // 2024년 June 6일 Wednesday AM 12:00:00.000000000
        val afterTime = LocalDate.of(2024, 6, 6).atTime(LocalTime.MIN)

        val test = testRepository.findById(testRepository.save(Test(time = beforeTime)).id).get()
        // 2024-06-06T00:00
        println(test.time)
        // before 시간이 after 시간과 동일한 것으로 판단됨
        assertEquals(test.time, afterTime)
    }

 

데이터 베이스에도 업데이트 되어 저장되는 것을 확인할 수 있었다. 

 

 

그렇다면 LocalDateTime.now()의 경우 어떻게 정확한 값으로 비교를 했던 것일까? LocalTime.Max()와 다르게 LocalDateTime.now()의 경우 나노초 이하 단위는 0이기 때문에 문제가 발생하지 않은 것이다! 

 

 

 

 

 

 

애플리케이션 레벨에서의 날짜, 시간 처리 예시 

또한 추가로, 데이터베이스 레벨에서만 날짜, 시간 처리가 들어가는 것은 아니다. 애플리케이션 레벨에서도 처리가 들어간다. 

 

 

1. mysql-connector

예를 들어 mysql-connector 자체적으로도 데이터 바인딩 시 MySQL 버전이 5.6.4 이하(serverSupportFracSec())거나 sendFractionalSeconds가 false로  fractionalSeconds를 지원하지 않으면 초 단위 미만은 버리고, 지원하는 경우엔 자리수에 따라 반올림을 한다. 

- 링크 : https://github.com/mysql/mysql-connector-j/blob/1c3f5c149e0bfe31c7fbeb24e2d260cd890972c4/src/main/protocol-impl/java/com/mysql/cj/protocol/a/AbstractValueEncoder.java#L121

protected LocalDateTime adjustLocalDateTime(LocalDateTime x, Field f) {
    if (!this.serverSession.getCapabilities().serverSupportsFracSecs() || !this.sendFractionalSeconds.getValue()) {
        if (x.getNano() > 0) {
            x = x.withNano(0); // truncate nanoseconds
        }
        return x;
    }
    return TimeUtil.adjustNanosPrecision(x, f == null ? 6 : f.getDecimals(), !this.serverSession.isServerTruncatesFracSecs());
}

 

 

 

 

2. JPA 파라미터 바인딩

또한 JPA에 의해 애플리케이션 단에서  파라미터 바인딩시에 fractional seconds가 정해진 데이터 타입의 범위(6자리)보다 더 넓은 범위라면 동일하게 반올림 되어 동일한 문제가 발생한다. 

 

파라미터에 localDateTime을 활용하는 예제를 구현해보자. 

@Repository
interface TestRepository : JpaRepository<Test, Long> {

    fun findAllByTimeLessThanEqual(time: LocalDateTime): List<Test>
}

 

테스트 코드를 보면, 데이터 형식에는 6월 5일 마지막 시간대와 6월 6일 시간대를 저장하고 6월 6일 이전의 데이터를 조회하도록 파라미터에 LocalDate.of(2024, 6, 5).atTime(LocalTime.MAX)를 활용하여 2024년 June 5일 Wednesday PM 11:59:59.999999999의 데이터를 넣을 수 있도록 했다.

    @Test
    @Rollback(false)
    fun test3() {
        // 2024년 June 5일 Wednesday PM 11:59:59.999999999
        val beforeTime = LocalDate.of(2024, 6, 5).atTime(LocalTime.MAX)

        // 2024년 June 6일 Wednesday AM 12:00:00.000000000
        val afterTime = LocalDate.of(2024, 6, 6).atTime(LocalTime.MIN)

        testRepository.saveAll(listOf(
            // 2024-06-05 23:59:59.999999
            Test(time = beforeTime.minusNanos(999)),
            // 2024-06-06 00:00:00.000000
            Test(time = afterTime)
        ))

        val results = testRepository.findAllByTimeLessThanEqual(beforeTime)
        // 2024-06-05T23:59:59.999999
        // 2024-06-06T00:00
        results.forEach { println(it.time) }
        // 테스트 실패 : 결과에 after 타임이 포함되어 검색으로 2개의 결과가 조회 
        assertThat(results).hasSize(1)
    }

 

 

테스트 결과 데이터에 6월 6일의 데이터가 포함되어 검색되는 것을 확인할 수 있다. 파라미터에 분명 6월 5일의 마지막 시간 데이터를 넣어 이전 날짜의 데이터만 조회하도록 했는데, 어떻게 6월 6일의 데이터가 포함되어 조회되는 것일까? 

 

이는 JPA가 파라미터 바인딩을 할 때, 날짜 데이터의 fractional seconds 단위가 6이상이면, 반올림을 처리하여 바인딩하기 때문이다. 

 

 

실제 바인딩을 하기 위해서 BasicBinder의 bind에 디버거 포인트를 걸고 findAllByTimeIsLessThanEqual메서드를 디버그 해보면, 

value가 2024-06-05T23:59:59.999999999로 찍히는 면서 else 구문으로 이동하여 doBind를 호출한다. 

 

	@Override
	public final void bind(PreparedStatement st, J value, int index, WrapperOptions options) throws SQLException {
		if ( value == null ) {
			if ( JdbcBindingLogging.LOGGER.isTraceEnabled() ) {
				JdbcBindingLogging.logNullBinding(
						index,
						jdbcType.getDefaultSqlTypeCode()
				);
			}
			doBindNull( st, index, options );
		}
		else {
			if ( JdbcBindingLogging.LOGGER.isTraceEnabled() ) {
				JdbcBindingLogging.logBinding(
						index,
						jdbcType.getDefaultSqlTypeCode(),
						getJavaType().extractLoggableRepresentation( value )
				);
			}
			doBind( st, value, index, options );
		}
	}

 

 

 

TimestampJdbcType.java의 doBind가 호출되면 else 구문으로 넘어가 st.setTimestamp를 호출하는데, 

 

 

이때, ClientPreparedStatement의 setTimestamp 메서드가 호출된다. 여기서는 setBinding를 호출하고, 

 

 

이때 NativeQueryBindValue의 setBinding 메서드가 실행되면서, setBinding 로직으로 넘어간다. 

 

 

여기서는 NativeQueryBindValue의 setBinding이 호출되는데, 이때 vc.get()이 호출되면서 SqlTimestampValueEncoder로 설정되는 것을 확인할 수 있다. 

this.valueEncoder = vc.get();

 

 

 

SqlTimestampValueEncoder를 자세히 보면 getString과 encodeAsBinary시에 상위 클래스인 AbstractValueEncoder의 adjustTimestamp을 호출하는 것을 살펴볼 수 있는데, 

 

package com.mysql.cj.protocol.a;

import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;
import java.util.concurrent.TimeUnit;

import com.mysql.cj.BindValue;
import com.mysql.cj.Messages;
import com.mysql.cj.MysqlType;
import com.mysql.cj.exceptions.ExceptionFactory;
import com.mysql.cj.exceptions.WrongArgumentException;
import com.mysql.cj.protocol.InternalDate;
import com.mysql.cj.protocol.InternalTime;
import com.mysql.cj.protocol.InternalTimestamp;
import com.mysql.cj.protocol.Message;
import com.mysql.cj.protocol.a.NativeConstants.IntegerDataType;
import com.mysql.cj.protocol.a.NativeConstants.StringSelfDataType;
import com.mysql.cj.util.StringUtils;
import com.mysql.cj.util.TimeUtil;

public class SqlTimestampValueEncoder extends AbstractValueEncoder {

    private SimpleDateFormat tsdf = null;

    @Override
    public String getString(BindValue binding) {
        Timestamp x = adjustTimestamp((Timestamp) ((Timestamp) binding.getValue()).clone(), binding.getField(), binding.keepOrigNanos());

 

 

이때 AbstractValueEncoder의 adjustTimestamp 메서드를 보면 fractional seconds를 지원하지 않는 서버세션의 경우에는 TimeUtil의 trauncateFractionalSeconds를 호출하고 지원하는 경우에는 TimeUtil의 adjustNanoPrecision을 호출하고 있다. 

 

 

여기서 TimeUtil의 truncateFractionalSeconds는 나노 초 단위를 리셋하고, TimeUtil의 adjustNanoPrecision을 확인하면 반올림 로직을 수행하는 것을 확인할 수 있다. 

 

 

 

따라서 결과적으로 ClientPreparedStatement의 setTimestamp 메서드가 종료되면 디버거의 this에 쿼리 파라미터로 날짜가 2024-06-06 00:00:00의 형식으로 업데이트 되어 파라미터가 반올림 되는 것을 확인할 수 있다. 

 

 

 

 

 

 

 

결론 

이로써, mysql 의 datetime의 시간 반올림 현상과 애플리케이션 레벨에서의 날짜, 시간 반올림 현상까지 디버깅과 테스트 코드를 통해 확인해보았다. 해당 타입을 사용하는 경우 시간 데이터 정확성을 위해 datetime의 fractional seconds를 이용할 수 있지만, 애플리케이션 레벨에서 JPA 파라미터나, 데이터를 넣을 때 넣고자 하는 데이터의 fractional seconds 자릿수를 일치시켜 반올림되는 현상이 발생하지 않도록 주의하자! 

 

 

 

 

 

 

 

 

+) 참고한 사이트  ✨

- https://lenditkr.github.io/MySQL/fractional-seconds-rouding-problem/

아니 시간도 반올림이 된다고?

- https://blog.naver.com/naverfinancial/223271699975

오늘자 데이터를 조회했는데 다음날 데이터가 딸려와요 (MySQL) | 네이버페이 기술블로그