来自客户端或服务器的时间戳 - 最佳实践是什么? [英] Timestamp from the client or the server - What's the best practice?

问题描述

我有一个 java 应用程序，它正在向我的 mysql 数据库中插入一些数据.到目前为止，我通过java代码从客户端机器获取了时间戳:

DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");日期插入日期=新日期();

然后在我准备好的语句中使用它

update 'table'set (status,insertDate) 值 (?,?)pst.setString(1, "成功");pst.setString(2, dateFormat.format(insertDate));

这工作正常，但是如果其中一个客户端弄乱了系统时间并将其设置为一天后怎么办.因此，我想使用我服务器的时间戳作为基本事实(插入发生的实际时间)并发现我可以使用 NOW().我修改了我的代码，现在看起来像这样

update 'table' set (status,insertDate) 值 (?,NOW())pst.setString(1, "成功");

这是完成这项工作的正确方法吗?我假设这将插入请求到达服务器并发生插入的时间戳，而不是直接从客户端获取时间戳.我的理解对吗?

解决方案

转到服务器端

如果您想在插入数据库时​​捕获当前时刻，当然最好的方法是在服务器上捕获该时刻.您可能比最终用户更信任您的服务器系统管理员，以确保他们的计算机时钟设置正确.

提示:如果您拥有强大的数据库服务器，通常最好将您可以完成的任何工作从您的应用转移到数据库服务器.

DEFAULT CURRENT_TIMESTAMP

更好的是，自动化.无需一直将当前时刻捕获写入 SQL 事务代码.

相反，定义一个

I have a java application which is inserting some data into my mysql database. So far I had the timestamp taken from the client machine through java code:

DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date insertDate= new Date();

and then use this in my prepared statement like

update 'table'set (status,insertDate) values (?,?)
pst.setString(1, "Success");
pst.setString(2, dateFormat.format(insertDate)); 

This is working fine, But what if one of the clients messes up with the system time and set it say, a day back. Hence I wanted to use my server's timestamp as the ground truth (actual time of when the inserts are happening) and found out i can use NOW(). I altered my code and it looks like this now

update 'table' set (status,insertDate) values (?,NOW())
pst.setString(1, "Success");

Is this the right way to get this done ? I assume this will insert the timestamp of when the request hits the server and the insert happens, rather than getting the timstamp directly from the client. Is my understanding right ?

解决方案

Go server-side

If you want to capture the current moment when inserted in the database, certainly the best approach is capturing that moment on the server. You can likely trust you server sys-admin more than your end-user to keep their computer clock set correctly.

Tip: Generally best to move any work you can from your app to the database server, if you have a powerful robust database server.

DEFAULT CURRENT_TIMESTAMP

Even better, automate. No need to write the current-moment-capture into your SQL transaction code all the time.

Instead, define a DEFAULT value on the server as part of defining the table and columns.

According to this doc, the function CURRENT_TIMESTAMP is standard SQL for capturing the current moment at the beginning of the current transaction as read from the server’s clock. I do not know of a standard-SQL command for capturing the current moment during the statement’s execution (in contrast to start-of-transaction).

The current moment should be stored in a column of a data type akin to the standard-SQL type TIMESTAMP WITH TIME ZONE.

This code may be close to standard SQL, and works in databases such as Postgres.

CREATE TABLE Person (
    pkey_ INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    surname_ VARCHAR(80) NOT NULL ,
    given_name_ VARCHAR(80) NOT NULL ,
    row_inserted_ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ;

Regarding MySQL specifically:

• Apparently in MySQL 8 the type TIMESTAMP WITH TIME ZONE would be TIMESTAMP.
• I gather that MySQL does not yet support the standard GENERATED… feature, so use AUTO_INCREMENT.
• This doc says MySQL 8 supports the standard function name CURRENT_TIMESTAMP. This is a synonym for the MySQL-specific NOW() function seen in the Question’s code. I suggest sticking to the standard whenever practical.

(I don't use MySQL, so verify details.)

Example for MySQL:

CREATE TABLE Person (
    pkey_ INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    surname_ VARCHAR(80) NOT NULL ,
    given_name_ VARCHAR(80) NOT NULL ,
    row_inserted_ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ;

Retrieve that auto-inserted value as an OffsetDateTime object.

OffsetDateTime odt = myResultSet.get( "row_inserted_" , OffsetDateTime.class ) ;

See that moment as perceived in the wall-clock time used by the people of a particular region (a time zone) rather than in UTC.

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = odt.withZoneSameInstant( z ) ;

这篇关于来自客户端或服务器的时间戳 - 最佳实践是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！