SQL中DateTime格式化及计算时间差（秒）的技术问询

Hey there! Let's break down solutions to your two SQL questions using the schema and sample data you provided.

Your Schema & Sample Data #

Here's your table creation and data insertion code formatted properly:

CREATE TABLE User (
    UserID int NOT NULL UNIQUE,
    Username Varchar(200),
    PRIMARY KEY (UserID)
);

CREATE TABLE Process (
    ProcessID int NOT NULL UNIQUE,
    ProcessName varchar(100),
    LastModifiedDate datetime,
    LastModifiedByUser int not null UNIQUE,
    PRIMARY KEY (ProcessID),
    FOREIGN KEY (LastModifiedByUser) REFERENCES User(UserID)
);

CREATE TABLE session (
    SessionID int NOT NULL UNIQUE,
    processID int NOT NULL UNIQUE,
    startdatetime datetime,
    stopdatetime datetime,
    startedbyuser int NOT NULL UNIQUE,
    PRIMARY KEY (sessionid),
    FOREIGN KEY (processid) REFERENCES process(ProcessID),
    FOREIGN KEY (startedbyuser) REFERENCES user (UserID)
);

INSERT INTO User (UserID, Username) VALUES ('1', 'XXX');
INSERT INTO User (UserID, Username) VALUES ('2', 'YYY');
INSERT INTO User (UserID, Username) VALUES ('3', 'ZZZ');

INSERT INTO Process (ProcessID, ProcessName, LastModifiedDate, LastModifiedByUser)
VALUES ('100', 'running the job', CAST(N'2012-06-18 10:34:09.000' AS DateTime), '1');
INSERT INTO Process (ProcessID, ProcessName, LastModifiedDate, LastModifiedByUser)
VALUES ('101', 'Forced stopped the application', CAST(N'2012-06-18 10:34:09.000' AS DateTime), '3');

INSERT INTO session (SessionID, processID, startdatetime, stopdatetime, startedbyuser)
VALUES ('1001', '101', CAST(N'2012-06-18 12:34:09.000' AS DateTime), CAST(N'2012-06-18 23:34:09.000' AS DateTime), '1');
INSERT INTO session (SessionID, processID, startdatetime, stopdatetime, startedbyuser)
VALUES ('1002', '100', CAST(N'2012-06-18 15:34:09.000' AS DateTime), CAST(N'2012-06-18 17:34:09.000' AS DateTime), '3');

Your Current Query #

Here's your existing SELECT statement, cleaned up for readability:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    session.startdatetime,
    session.stopdatetime,
    Process.LastModifiedDate,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

Question 1: Formatting DateTime Columns to a Specified Date Format #

DateTime formatting varies by SQL dialect—here are solutions for the most common databases, using your query as a base:

SQL Server #

Use CONVERT() (for standard pre-defined formats) or FORMAT() (for fully custom, human-readable patterns):

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    -- Format startdatetime to 'yyyy-MM-dd HH:mm:ss' (ODBC canonical format)
    CONVERT(varchar, session.startdatetime, 120) AS formatted_start,
    -- Format LastModifiedDate to 'dd-MMM-yyyy' (e.g., 18-Jun-2012)
    FORMAT(Process.LastModifiedDate, 'dd-MMM-yyyy') AS formatted_modified_date,
    session.stopdatetime,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

• CONVERT() uses numeric style codes (you can look up codes for other standard formats)
• FORMAT() lets you define any pattern you want, like 'yyyy/MM/dd' or 'hh:mm tt' for AM/PM time

MySQL #

Use DATE_FORMAT() to define your desired format with specifiers:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    DATE_FORMAT(session.startdatetime, '%m/%d/%Y %H:%i:%s') AS formatted_start,
    DATE_FORMAT(Process.LastModifiedDate, '%d-%b-%Y') AS formatted_modified_date,
    session.stopdatetime,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

• Common specifiers: %Y (4-digit year), %m (2-digit month), %d (2-digit day), %H (24-hour hour), %i (minutes), %s (seconds)

PostgreSQL #

Use TO_CHAR() to convert datetime values to custom formatted strings:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    TO_CHAR(session.startdatetime, 'MM/DD/YYYY HH24:MI:SS') AS formatted_start,
    TO_CHAR(Process.LastModifiedDate, 'DD-Mon-YYYY') AS formatted_modified_date,
    session.stopdatetime,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

• Use HH24 for 24-hour time, Mon for abbreviated month names, and adjust the pattern to match your needs

Question 2: Calculating Duration Between Start and End Times (in Seconds) #

Calculating time differences also depends on your database—here's how to get the total seconds between startdatetime and stopdatetime:

SQL Server #

Use DATEDIFF() with the second parameter to get the total seconds between two datetimes:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    session.startdatetime,
    session.stopdatetime,
    -- Calculate total seconds between start and stop
    DATEDIFF(second, session.startdatetime, session.stopdatetime) AS session_duration_seconds,
    Process.LastModifiedDate,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

MySQL #

Use TIMESTAMPDIFF() with SECOND as the target unit:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    session.startdatetime,
    session.stopdatetime,
    TIMESTAMPDIFF(SECOND, session.startdatetime, session.stopdatetime) AS session_duration_seconds,
    Process.LastModifiedDate,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

PostgreSQL #

Subtract the start time from the end time, then extract the epoch (total seconds since 1970-01-01) to get the duration:

SELECT 
    Process.ProcessID,
    session.startedbyuser,
    session.startdatetime,
    session.stopdatetime,
    EXTRACT(EPOCH FROM (session.stopdatetime - session.startdatetime)) AS session_duration_seconds,
    Process.LastModifiedDate,
    process.LastModifiedByUser
FROM Process
INNER JOIN session ON Process.ProcessID = session.ProcessID;

This returns a numeric value representing the exact total seconds between the two times, including fractional seconds if present.

内容的提问来源于stack exchange，提问作者sasikals26