如何在SQLITE中使用AM/PM按日期/时间订购

plicqrtu  于 2022-12-29  发布在  SQLite
关注(0)|答案(3)|浏览(136)

我的日期格式是:“年-月-日”(2017年3月23日)
我的时间格式是:“时:分a”(晚上10时15分)
如果在MYSQL你可以执行这个转换时间与上午/下午:

SELECT * FROM table_name ORDER BY STR_TO_DATE(timeField,'%h.%i%p');

如何在SQLITE中执行此操作?
我试过了,但没有用:

SELECT appointment_date, start_time FROM appointment order by appointment_date, DATE(start_time, '%h:%i %p')

结果:Image Link
假设AM应该是第一个比PM,因为默认值是ASC,我尝试使用DESC以及,但它没有正确安排的结果。

lf5gs5x2

lf5gs5x21#

您似乎将start_time()值存储在字符串中。
您可以:

order by appointment_date,
         (case when start_time like '% am' then 1 else 2 end),
         start_time

SQLite并不真正支持日期/时间格式的am/pm,但是使用like可以很容易地实现这一点。

kxeu7u2r

kxeu7u2r2#

更好的解决方案是将时间以24小时格式存储在多一列中,并且该列的类型应该是DATETIME而不是varchar。
您可以使用以下方法转换时间:

SimpleDateFormat displayFormat = new SimpleDateFormat("HH:mm");
        SimpleDateFormat parseFormat = new SimpleDateFormat("hh:mma");
        Date date=new Date();
        try {
            date = parseFormat.parse(AmPmTimeFormatColumn);
        }catch (Exception e){}
        values.put("24HourFormatColumn", displayFormat.format(date));

然后像这样订购:
从按日期时间(24小时格式列)排序的表名中选择 *;

piv4azn7

piv4azn73#

select
    Date
    , cast(substr(Date, 12, 2) as int) as HOUR
    , substr(Date, 21, 2) as TIME_FORMAT
    , 
        case
            when 
                substr(Date, 21, 2) = 'PM' AND cast(substr(Date, 12, 2) as int) = 12
                then cast(substr(Date, 12, 2) as int)
            when 
                substr(Date, 21, 2) = 'PM'
                then cast(substr(Date, 12, 2) as int) + 12
            when 
                substr(Date, 21, 2) = 'AM' AND cast(substr(Date, 12, 2) as int) = 12
                then 0

            when 
                substr(Date, 21, 2) = 'AM'
                then cast(substr(Date, 12, 2) as int)
        END AS ARMY_HOUR
    , cast(substr(Date, 15, 2) as int) AS MINUTE
from appointment

相关问题