Common Function usage in SQL Server, MySQL, Oracle, and PostgreSQL (1)

Using Hibernate in practice does not use the MySQL database, but uses PostgreSQL that was recently used. Because different data has their own functions for the same operation, MySQL's date_format (), there is no such thing in PostgreSQL. google found that to_char () was used (). I found an English article "ExecutingCommonSQLCoding ".

Using Hibernate in practice does not use the MySQL database, but uses PostgreSQL that was recently used. Because different data has their own functions for the same operation, MySQL's date_format (), there is no such thing in PostgreSQL. google found that to_char () was used (). I found an English article titled Executing Common SQL Coding.

Using Hibernate in practice does not use the MySQL database, but uses PostgreSQL that was recently used. Because different data has their own functions for the same operation, MySQL's date_format (), there is no such thing in PostgreSQL. google found that to_char () was used ().

I found an English article "Executing Common SQL Coding Tasks Using Function CALS" (differences between Common SQL Function Calls), which lists such as ms SQL Server, MySQL, Oracle, and PostgreSQL, common functions in these common databases.

Edge translators add their own supplements and share them with you in several articles. Convenience for future use.

Date operation

1. Get the current date and time

• SQL Server

Getdate () function (returns the current date and time)

SELECT GETDATE()GO

• MySQL

Curdate () function (returns the current date)

Now () function (returns the current date and time)

SELECT CURDATE()

• Oracle

Sysdate (return server time)

SELECT SYSDATE FROM dual;

• PostgreSQL

Current_date (returns the date of the current day)

Current_timestamp (return date and time)

Now () (returns the current date and time, equivalent to current_timestamp)

SELECT CURRENT_DATE;

Note]

1. The above functions do not require parameters.
2. In Oracle, current_date and sysdate are the actual current time, and the results are basically the same, but there are also differences:
   A. urrent_date returns the current session time, And sysdate returns the server time.
   B. current_date is sometimes one second faster than sysdate, which may be the result of rounding.
   C. if you change the time zone of the current session, for example, change China's time zone to UTC + 9, then the time displayed by current_date is UTC + 9. According to the principle of UTC + Xi subtraction, current_date should be one hour faster than sysdate.
   

2. Obtain the subdomain of the operation time. For example, year, month, day, hour, etc.

• SQL Server:Datepart (datepart, date)

SELECT DATEPART(dw, GETDATE())GO

* The datepart () function can easily retrieve all parts of a date, for example, Date: 15:15:36. 513

Yy	Year: 2012
Mm	Month: 12
Dd	Days in the month: 5
Dy	Days in the year: 340
Wk	Week in the year: 50
Dw	Days in the week: 4
Qq	Quarter of the year: 4
Hh	Hours: 15
Mi	Minute: 15
Ss	Seconds: 36

• MySQL:Dayofmonth (date) returns the name of the corresponding workday

Select dayname ('2017-02-03 ');-> 'thurs'

• Oracle:To_char (date, 'format ')

SELECT TO_CHAR(SYSDATE, 'Day') FROM dual;

• PostgreSQL:Date_part (text, timestamp)

SELECT DATE_PART('dow', date 'now');

3. Two Time Intervals

• SQLServer

SELECT DATADIFF(dd,'1/1/01',GETDATE())GO

• MySQL

SELECT FROM_DAYS(TO_DAYS(CURDATE()) - TO_DAYS('2012-12-05'));

• Oracle

SELECT TO_DATE('25-Nov-2000','dd-mon-yyyy') - TO_DATE('25-Aug-1969','dd-mon-yyyy')FROM dual;

• PostgreSQL

SELECT AGE(CURRENT_DATE,'25-Aug-1969');

4. Date formatting (Mon, DD, YYYY; mm/dd/yy; dd/mm/yy; etc)

• SQL Server

SELECT CONVERT(VARCHAR(11),GETDATE(),102)GO

• MySQL

SELECT DATE_FORMAT("2001-11-25","%M %E,%Y");

• Oracle

ELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy hh:mi,ss PM')FROM dual;

• PostgreSQL

SELECT TO_CHAR(timestamp(CURRENT_DATE),'dd-Mon-yyyy hh:mi:ss PM')