MySql - 按日期然后按时间排序

MySql - Order by date and then by time

提问by AndreMiranda

I have a table with 2 'datetime' fields: 'eventDate' and 'eventHour'. I'm trying to order by 'eventDate' and then by 'eventHour'.

我有一个包含 2 个“日期时间”字段的表：“eventDate”和“eventHour”。我正在尝试按“eventDate”和“eventHour”进行订购。

For each date I will have a list os events, so that's why I will to order by date and then by time.

对于每个日期，我都会有一个列表 os 事件，这就是为什么我会先按日期然后按时间排序。

thanks!!

谢谢！！

回答by paxdiablo

I'm not sure whether there's any hidden meaning in your question but the standard way of doing this seems to fit:

我不确定您的问题是否有任何隐藏的含义，但这样做的标准方法似乎很合适：

... order by eventDate, eventHour

That gives you hours within dates, like:

这为您提供了日期内的小时数，例如：

Feb 15
    09:00
    12:00
    17:00
Feb 23
    22:00
    : :

If you actually have those two fields as realdatetimefields, your schema is screwed up. You should have a datefield for the date and a timeor integral field for the hour.

如果您确实将这两个字段作为真正的datetime字段，那么您的架构就搞砸了。您应该有一个date用于日期的字段和一个time用于小时的或整数字段。

You couldcombine both into a single datetimefield but you should balance that against the inefficiencies of doing per-row functions in your selectstatements if you want to separate them. It's usually better to keep fields separate if your going to use them distinctly.

您可以将两者合并到一个datetime字段中，但select如果您想将它们分开，您应该平衡它与在语句中执行每行函数的低效率。如果您要明确使用它们，通常最好将字段分开。

If you dohave to use per-row functions, you can use:

如果你也必须使用每行的功能，你可以使用：

date(datetime_column)
time(datetime_column)

to extract just the dateand timecomponents of a datetime.

只提取a的date和time组件datetime。

回答by Romancha KC

If you want to list out data as latest eventdate first, if records of same date exists then data will be sorted by time ie latest time first,,

如果您想首先将数据列为最新的事件日期，如果存在相同日期的记录，则数据将按时间排序，即最晚时间在前，，

You can try as below

您可以尝试如下

select * from my_table order by eventDate DESC, eventHour DESC

回答by Matt Mitchell

Why not save both the eventDateand eventHourinto the same field if they are both DateTime?

为什么不节省的eventDate和eventHour成相同的字段，如果他们都是DateTime？

To get what you want with the current scheme you can do this:

要使用当前方案获得您想要的东西，您可以执行以下操作：

SELECT * FROM table 
  ORDER BY 
    EXTRACT(YEAR_MONTH FROM eventDate),
    EXTRACT(DAY FROM eventDate),
    EXTRACT(HOUR FROM eventHour)

回答by Ned Batchelder

Does

做

select * from my_table order by eventDate, eventHour

not work?

不行？

回答by northpole

You can order by as many as you need, just use a list. It will order by the first value, then the second...etc.

您可以根据需要订购任意数量，只需使用列表即可。它将按第一个值排序，然后是第二个......等等。

  SELECT *
    FROM table
ORDER BY eventDate, eventHour

If you have two datetime fields then you can get just the date or just the hour using something like:

如果您有两个日期时间字段，那么您可以使用以下内容仅获取日期或小时：

    SELECT *
      FROM table
  ORDER BY DATE_FORMAT(eventDate, '%d'), DATE_FORMAT(eventHour, '%h')

回答by JMD

Order it by the id in descending order.

按 id 降序排列。

<?php $sql = "SELECT * FROM posts ORDER BY id DESC?>

<?php $sql = "SELECT * FROM posts ORDER BY id DESC?>

This should work

这应该工作

回答by David ddc

This is the best way that worked for me, just convert date to numbers of days whith the function to_days(date)and time to amount of seconds whith the function TIME_TO_SEC(time), example:

这是为我工作的最好办法，只是转换日期的天数蒙山功能TO_DAYS（日期）和时间的秒数蒙山功能TIME_TO_SEC（时间），例如：

SELECT 
    nv.destac,
    DATE_FORMAT(nv.date_nov , '%d-%m-%Y %T') as date_order, 
    TO_DAYS(nv.date_nov) as days,
    TIME_TO_SEC(TIME(nv.date_nov)) as seconds

FROM 
    newsviews nv
WHERE 
     nv.active
ORDER BY 
      nv.destac DESC,  days DESC, seconds DESC ;

Esta es la mejor forma que funcionó para mí, sólo convertir la fecha al número de días con la función TO_DAYS (fecha)y el tiempo a la cantidad de segundos con la funcion TIME_TO_SEC (tiempo)

Esta es la mejor forma que funcionó para mí, sólo convertir la fecha al número de días con la función TO_DAYS (fecha)y el tiempo a la cantidad de segundos con la funcion TIME_TO_SEC (tiempo)

SELECT 
    nv.destacar,
    DATE_FORMAT(nv.fecha_nov , '%d-%m-%Y %T') as fecha_orden, 
    TO_DAYS(nv.fecha_nov) as dias,
    TIME_TO_SEC(TIME(nv.fecha_nov)) as segundos

FROM 
    novedades nv
WHERE 
     nv.activa
ORDER BY 
      nv.destacar DESC,  dias DESC, segundos DESC ;

回答by Gilberto Sánchez

why not to use a TIMESTAMP to join the both fields?

为什么不使用 TIMESTAMP 加入这两个字段？

First, we need to convert the eventDate field to DATE, for this, we'll use the DATE() function:

首先，我们需要将 eventDate 字段转换为 DATE，为此，我们将使用 DATE() 函数：

SELECT DATE( eventDate );

After that, convert the eventHour to TIME, something like this:

之后，将 eventHour 转换为 TIME，如下所示：

SELECT TIME( eventHour );

The next step is to join this two functions into a TIMESTAMP function:

下一步是将这两个函数连接成一个 TIMESTAMP 函数：

SELECT TIMESTAMP( DATE( eventDate ), TIME( eventHour ) );

Yeah, but you don't need a SELECT, but an ORDER BY, so a complete example will be like this:

是的，但是你不需要 SELECT，而是一个 ORDER BY，所以一个完整的例子是这样的：

SELECT e.*, d.eventDate, t.eventHour
FROM event e
JOIN eventDate d
     ON e.id = d.id
JOIN eventTime t
     ON e.id = t.id
ORDER BY TIMESTAMP( DATE( d.eventDate ), TIME( t.eventHour ) );

I hope this can help you.

我希望这可以帮助你。

回答by irshad

Firstly, it is bad practice to save date and time in different fields.

首先，在不同领域保存日期和时间是不好的做法。

Anyways, assuming you are saving date/time in the proper format. (i.e: Date format is yyyy-mm-dd and Time is hh:ss)

无论如何，假设您以正确的格式保存日期/时间。（即：日期格式为 yyyy-mm-dd，时间为 hh:ss）

You first need to concatenate the date and time to get a datetime value. Technically speaking you can do a ORDER BY ON a Datetime field, but it is not good practice again. In our case when we run a CONCAT, so this is converted to a string so the resultset will be wrong. So you need to convert it to a UNIX TIMESTAMP to do a ordering.

您首先需要连接日期和时间以获得日期时间值。从技术上讲，您可以在日期时间字段上执行 ORDER BY，但这又不是一个好习惯。在我们的例子中，当我们运行一个 CONCAT 时，它被转换为一个字符串，因此结果集将是错误的。因此，您需要将其转换为 UNIX TIMESTAMP 以进行排序。

Its good practice to run a UNIX_TIMESTAMP on a datetime field before a ORDER by clause.

在 ORDER by 子句之前在日期时间字段上运行 UNIX_TIMESTAMP 是一种很好的做法。

You can use the following query.

您可以使用以下查询。

SELECT column1,column2, UNIX_TIMESTAMP(CONCAT(event_date,' ',event_time)) as unixtimestampfrom table_name Order by unixtimestamp desc;

SELECT column1,column2, UNIX_TIMESTAMP(CONCAT( event_date,' ', event_time)) as unixtimestampfrom table_name Order by unixtimestamp desc;

Note:(There is a space between event_date and event_time in the CONCAT function. It is not showing up correctly here.)

注意：（CONCAT 函数中 event_date 和 event_time 之间有一个空格。它在这里显示不正确。）