SQL中数字转日期与每日日期数据提取的技术求助

Hey there! Glad to help out with your SQL questions. Since you didn't share your specific table structures, I'll use generic examples that you can tweak to fit your actual data setup. Let's dive in!

The approach here depends on what kind of numeric date representation you're working with (e.g., YYYYMMDD like 20240520, or Unix timestamps like 1716182400) and your database system. Here are common scenarios:

Numeric YYYYMMDD format #

• MySQL: Use the STR_TO_DATE() function to parse the number as a string with the date format:

  SELECT STR_TO_DATE(20240520, '%Y%m%d') AS converted_date;
  

• SQL Server: Convert the number to a string first, then cast to a DATE type:

  SELECT CONVERT(DATE, CAST(20240520 AS VARCHAR(8))) AS converted_date;
  -- Alternative:
  SELECT CAST(CAST(20240520 AS VARCHAR(8)) AS DATE) AS converted_date;
  

• PostgreSQL: Cast the number to text, then use TO_DATE() to parse it:

  SELECT TO_DATE(20240520::TEXT, 'YYYYMMDD') AS converted_date;
  

Unix Timestamp (numeric seconds since 1970-01-01) #

• MySQL: Use FROM_UNIXTIME() to convert the timestamp, then cast to DATE if needed:

  SELECT FROM_UNIXTIME(1716182400) AS converted_datetime;
  -- Get just the date part:
  SELECT DATE(FROM_UNIXTIME(1716182400)) AS converted_date;
  

• SQL Server: Add the timestamp (in seconds) to the Unix epoch date:

  SELECT DATEADD(SECOND, 1716182400, '1970-01-01') AS converted_datetime;
  -- Get just the date part:
  SELECT CAST(DATEADD(SECOND, 1716182400, '1970-01-01') AS DATE) AS converted_date;
  

• PostgreSQL: Use TO_TIMESTAMP() then cast to DATE:

  SELECT TO_TIMESTAMP(1716182400) AS converted_datetime;
  -- Get just the date part:
  SELECT TO_TIMESTAMP(1716182400)::DATE AS converted_date;
  

This usually involves grouping your data by the date component, whether your table has a DATE column or a DATETIME/TIMESTAMP column.

If your table has a DATE type column #

Suppose you have a sales table with order_date (DATE) and amount columns. To get daily totals:

SELECT 
  order_date,
  SUM(amount) AS total_daily_sales,
  COUNT(*) AS total_orders
FROM sales
-- Optional: Filter for a date range
WHERE order_date BETWEEN '2024-01-01' AND '2024-05-20'
GROUP BY order_date
ORDER BY order_date;

If your table has a DATETIME/TIMESTAMP type column #

You'll need to truncate the datetime value to just the date part first, then group by that:

• MySQL: Use the DATE() function to extract the date part:

  SELECT 
    DATE(order_datetime) AS order_date,
    SUM(amount) AS total_daily_sales
  FROM sales
  GROUP BY DATE(order_datetime)
  ORDER BY order_date;
  

• SQL Server: Cast the datetime to a DATE type:

  SELECT 
    CAST(order_datetime AS DATE) AS order_date,
    SUM(amount) AS total_daily_sales
  FROM sales
  GROUP BY CAST(order_datetime AS DATE)
  ORDER BY order_date;
  

• PostgreSQL: Either cast to DATE or use DATE_TRUNC():

  -- Simple cast
  SELECT 
    order_timestamp::DATE AS order_date,
    SUM(amount) AS total_daily_sales
  FROM sales
  GROUP BY order_timestamp::DATE
  ORDER BY order_date;
  
  -- Using DATE_TRUNC (useful if you need to keep timestamp precision for other groupings later)
  SELECT 
    DATE_TRUNC('day', order_timestamp)::DATE AS order_date,
    SUM(amount) AS total_daily_sales
  FROM sales
  GROUP BY DATE_TRUNC('day', order_timestamp)
  ORDER BY order_date;
  

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