我的日期格式是:"yyyy-MM-dd" (2017-03-23)
我的时间格式是:"hh:mm a"(晚上 10:15)
如果在 MYSQL 中,您可以执行此操作以使用 am/pm 转换时间:
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')
结果:图片链接
据说 AM 应该比 PM 更先,因为默认值是 ASC,我也尝试使用 DESC,但它没有正确安排结果。
您似乎将start_time()
值存储在字符串中。
你可以做:
order by appointment_date,
(case when start_time like '% am' then 1 else 2 end),
start_time
SQLite并不真正支持日期/时间格式的am/pm。 但是,这很容易通过like
完成。
更好的解决方案是以 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));
然后像这样订购:
从table_name顺序中选择 * 按日期时间(24 小时格式列);
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