SQLite3如何将美式格式日期转换为Epoch时间戳？

Absolutely! SQLite has powerful built-in date and time functions that make converting between American-style dates and Unix timestamps (Epoch time) totally doable. Let’s break down the solutions based on your specific use case.

Scenario 1: Your date is in YYYY-MM-DD HH:MM:SS format (common but sometimes confused with "American-style") #

If your input date is already in the ISO 8601 format like 2018-03-31 01:02:03, converting it to a Unix timestamp is straightforward using the strftime() function. This function returns the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC) when using the %s format specifier.

Here’s how your corrected INSERT statement would look:

INSERT INTO candles_USD_BCH (id, timestamp) 
VALUES (null, strftime('%s', '2018-03-31 01:02:03'));

Note: Always wrap date strings in single quotes—without them, SQLite will treat the date as a mathematical expression (e.g., 2018-03-31 becomes 1984) which is definitely not what you want!

Scenario 2: Your date is in true American MM/DD/YYYY HH:MM:SS format #

If your date uses the classic American format like 03/31/2018 01:02:03, you’ll first need to rearrange the components into SQLite’s recognizable YYYY-MM-DD format. Use the substr() function to extract and recombine the date parts, then pass that to strftime().

Here’s the modified INSERT statement:

INSERT INTO candles_USD_BCH (id, timestamp) 
VALUES (null, 
        strftime('%s', 
                 -- Reformat MM/DD/YYYY to YYYY-MM-DD
                 substr('03/31/2018 01:02:03', 7, 4) || '-' ||  -- Extract year
                 substr('03/31/2018 01:02:03', 1, 2) || '-' ||  -- Extract month
                 substr('03/31/2018 01:02:03', 4, 2) || ' ' ||  -- Extract day
                 substr('03/31/2018 01:02:03', 12)  -- Extract time part
                )
       );

Bonus: Converting Unix timestamps back to American-style dates #

If you ever need to reverse the process (query stored timestamps and display them as American dates), use strftime() again with the unixepoch modifier to tell SQLite the input is a timestamp:

SELECT 
  id,
  strftime('%m/%d/%Y %H:%M:%S', timestamp, 'unixepoch') AS american_date
FROM candles_USD_BCH;

Handling Time Zones #

By default, SQLite assumes dates are in UTC. If your input dates are in local time, add the localtime modifier to adjust:

• Convert local American date to Unix timestamp:

  strftime('%s', '2018-03-31 01:02:03', 'localtime')
  

• Convert Unix timestamp to local American date:

  strftime('%m/%d/%Y %H:%M:%S', timestamp, 'unixepoch', 'localtime')
  

内容的提问来源于stack exchange，提问作者J. Doe