数据库迁移 - 可以多次运行的UTC转换脚本

发布于 2024-12-05 05:28:38 字数 450 浏览 0 评论 0原文

我正在编写一个脚本，将日期时间列从服务器时间转换为 UTC 时间。我有一个函数可以执行此操作，但是，迁移脚本将作为更大进程的一部分运行，并具有以下约束：

该脚本必须能够运行多次（即不要转换列两次）
不能离开迁移后的临时表或其他数据

这是到目前为止的脚本：

SET XACT_ABORT ON;

BEGIN TRANSACTION;

UPDATE SOME_TABLE
SET LastModified = [dbo].[f_ServerToUTC]( LastModified )

COMMIT TRANSACTION;

由于毫秒在我的场景中并不重要，因此我考虑将毫秒部分设置为某个特定值，表明迁移已经执行。然而，我觉得在未转换的数据中遇到这个值的概率太高了（足够了）。

鉴于我的限制，是否有其他方法可以表明脚本已运行？

原文

I'm writing a script that converts a datetime column from server time to UTC time. I have a function that does this, however, the migration script will be run as part of a larger process with the following constraints:

The script must be able to run multiple times (i.e. don't convert the column twice)
Can't leave temp tables or other data around after the migration

This is the script so far:

SET XACT_ABORT ON;

BEGIN TRANSACTION;

UPDATE SOME_TABLE
SET LastModified = [dbo].[f_ServerToUTC]( LastModified )

COMMIT TRANSACTION;

Since the milliseconds are not important in my scenario, I've considered setting the millisecond portion to some specific value, indicating that the migration has already been performed. However, I feel like the probability of encountering this value in unconverted data is too high (given enough).

Is there some other way I can signify that the script has been run, given my constraints?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

oО清风挽发oО 2024-12-12 05:28:38

我们解决这个问题的方法对于我们的系统来说有些特殊，但可能对其他人有用。我们有一个用户定义类型 UtcDateTime，定义为：

CREATE TYPE [dbo].[UtcDateTime] FROM [datetime] NOT NULL

由于我们将列更新为 UTC 而不是服务器时间，因此更改数据类型也是有意义的。因此，我们可以检查列上的数据类型是否已更改，以防止多次运行转换。

IF NOT EXISTS(
    SELECT 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    INNER JOIN sys.types ty
        ON c.user_type_id = ty.user_type_id
    WHERE t.object_id = object_id( 'SOME_TABLE' )
        AND c.name = 'LastModified'
        AND ty.name = 'utcdatetime'
)
BEGIN

    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    ALTER TABLE SOME_TABLE
        ALTER COLUMN [LastModified] UTCDATETIME

    UPDATE SOME_TABLE
    SET LastModified = [dbo].[f_ServerToUTC]( LastModified )

    COMMIT TRANSACTION;

END

The way we solved this is somewhat particular to our system, but may be useful to others. We have a User-Defined Type, UtcDateTime, defined as:

CREATE TYPE [dbo].[UtcDateTime] FROM [datetime] NOT NULL

Since we're updating the column to be UTC instead of server time, it made sense to change the data type as well. Therefore, we could check to see if the data type had already been changed on the column as a guard against running the conversion more than once.

IF NOT EXISTS(
    SELECT 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    INNER JOIN sys.types ty
        ON c.user_type_id = ty.user_type_id
    WHERE t.object_id = object_id( 'SOME_TABLE' )
        AND c.name = 'LastModified'
        AND ty.name = 'utcdatetime'
)
BEGIN

    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    ALTER TABLE SOME_TABLE
        ALTER COLUMN [LastModified] UTCDATETIME

    UPDATE SOME_TABLE
    SET LastModified = [dbo].[f_ServerToUTC]( LastModified )

    COMMIT TRANSACTION;

END

回复收藏 0 原文

~没有更多了~