postgresql's now() is the current transaction start time.
Oracle's sysdate is the current time.
The difference is in transactions.
postgresqlIn-housenow()： postgres=# begin ; BEGIN postgres=# select now(); now ------------------------------- 2017-03-31 14:28:32.403869+08 (1 row) postgres=# select now(); now ------------------------------- 2017-03-31 14:28:32.403869+08 (1 row) postgres=# select now(); now ------------------------------- 2017-03-31 14:28:32.403869+08 (1 row) postgres=# end; COMMIT postgres=# select now(); now ------------------------------- 2017-03-31 14:28:54.917897+08 (1 row) postgres=#
The sysdate in Oracle keeps changing in transactions.
Summarize:
- Postgresql's now() is the current transaction start time. If you call now(), the transaction start time will be obtained, rather than the current system time.
- Oracle's sysdate is to get the current time. When to call sysdate, it is when to get the time.
Supplement: The use of the now() function in mysql and the sysdate of oracle, which may be buried.
The use of the now() function in mysql, and the sysdate of oracle
In the requirements, if the system adds the current operation time, it is very simple. When writing SQL, just write the now() function directly at the corresponding position of this field. This will reduce a lot of code, especially when writing with jdbc, you can write less code about placeholders. However, there is an implicit premise that the database and server are on the same machine. If they are not on the same machine, then writing like this is a pit. I think there is no experience and people who have not fallen into the pit will write like this, for example, if I am lazy, I will write like this. When this time is used to compare or as the basis for judgment of business logic, this pit will appear.
lz encountered it when locating problems when doing business. At that time, the server was on lz's computer and the database was in the remote end. Because of the business needs, the time was adjusted to a future time point and then started to do it. But after about a week, I accidentally discovered that the time used by a table was actually the time. I had such a guess that a programmer should use the sysdate field. Although it was not obviously found, it should be used, otherwise the real time would not be taken. Especially for some projects that require remote debugging, my colleagues think that the sql encoding specification should also add this item.
lz did this before and did not understand it. Now he can only remind everyone not to do this and then change the code he wrote recently.
It is recommended to use Java to generate a new time object. If you use jdbc and directly splice it in SQL, you can do not use placeholders.
//Write this in daomember(name,pw,register_time)value(?,?,'"+(new Date())+"');";
//The database assistant class defines an SDF classpublic class DbAssitor
{
/** The operation result in database-related operations does not affect the number of rows ***/
public static int NO_AFFECT_ROW = 0;
public static String sdfyyyyMMddHHmmss_ = "yyyy-MM-dd HH:mm:ss";
public static SimpleDateFormat sdfyyyyMMddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.