CODEWITHSUNDEEP
javamysql
Adam Smith
Adam Smith

Reputation: 79

Timestamp from the client or the server - What's the best practice?

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 ?

Upvotes: 2

Views: 3985

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 340200

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

enter image description here

Upvotes: 2

Related Questions