如何在SQL中从timestamp字段提取星期几并按天统计数据

Got it! To extract the day of the week from your created timestamp column and aggregate counts per day, here's how you can do it across common SQL dialects:

PostgreSQL #

Use the to_char() function with 'FMDay' to get clean, un-padded day names:

SELECT 
  to_char(created, 'FMDay') AS day_of_week,
  COUNT(*) AS total_entries
FROM surveys
GROUP BY day_of_week
ORDER BY total_entries DESC;

MySQL #

Leverage the built-in DAYNAME() function which directly returns the full name of the day:

SELECT 
  DAYNAME(created) AS day_of_week,
  COUNT(*) AS total_entries
FROM surveys
GROUP BY day_of_week
ORDER BY total_entries DESC;

SQL Server #

Use DATENAME() with the weekday argument to fetch the day name:

SELECT 
  DATENAME(weekday, created) AS day_of_week,
  COUNT(*) AS total_entries
FROM surveys
GROUP BY DATENAME(weekday, created)
ORDER BY total_entries DESC;

Result for Your Sample Data #

When you run any of these queries against your provided data, you'll get the following output (formatted as you requested):

• Monday - 4
• Tuesday - 3
• Friday - 1

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