order by Time using current time?

Question

I haven't knowledge about DBA . I am a ios developer i have one question for DBA .

`SELECT StartDate,Subject
 FROM tbl_Calendar
 WHERE StartDate BETWEEN ` date('2017-04-25') AND ('2017-04-26')

Ex : 1

if current time is 13:30:00

give me this order 3,4,5,2,1

Ex : 2

if current time is 12:35:00

give me this order 2,3,4,5,1

Ex : 3

if current time is 11:35:00

give me this order 1,2,3,4,5

This is my Table sql :

  CREATE TABLE `tbl_Calendar` (
    `StartDate` datetime,
    `Subject`   ntext
  );

Any help will be much appreciated.

what's the reasoning behind this ordering? absolute difference? — Vamsi Prabhala, Apr 25 '17 at 12:48
This is a event sedule so if current time is 12:30 it means 12:00 work on going (print 1st )and next work is 1:00 (print second) and next or next and last work is finish — Harshil Kotecha, Apr 25 '17 at 12:51
If you don't specify an `ORDER BY` clause, the ordering is effectively undefined (however much it looks reproducible). If you _want_ a specific order, add an appropriate `ORDER BY`. — TripeHound, Apr 25 '17 at 12:51
Just to clarify if this is to be used for **event schedule** why would you require the values 2 and 1 for your example 1. As its already past that time. — DataWrangler, Apr 25 '17 at 12:54

Filipe Martins · Accepted Answer · 2017-04-26T11:43:02.167

2

You could use CASE in your ORDER BY like this:

    SELECT 
        StartDate, Subject
    FROM   
        tbl_Calendar
    WHERE  
        StartDate BETWEEN DATE ('2017-04-25') AND ('2017-04-26')
    ORDER  BY 
        CASE WHEN ((StartDate < DATETIME('now', 'localtime')) AND (StartDate <> (SELECT StartDate FROM tbl_Calendar WHERE  StartDate < DATETIME ('now', 'localtime') ORDER  BY StartDate desc limit 1))) THEN DATETIME (StartDate, '+1 day') ELSE StartDate END;

The subselect inside CASE will ignore the running event record and change only the previous ones.

edited Apr 26 '17 at 11:43

answered Apr 25 '17 at 13:15

Filipe Martins

608
10
23

@HarshilKotecha No problem. Glad to help you. – Filipe Martins Apr 25 '17 at 13:28
current time stamp give 13:31 please check . how to set time zone – Harshil Kotecha Apr 25 '17 at 13:31
i think this is good StartDate < datetime('now','localtime') ? – Harshil Kotecha Apr 25 '17 at 13:33
if you knowledge which **question headline** good please suggest or edit – Harshil Kotecha Apr 25 '17 at 13:36
now new query not work conver datetime to timestamp ? – Harshil Kotecha Apr 25 '17 at 13:37
datetime('now','localtime') this function give me a date or time – Harshil Kotecha Apr 25 '17 at 13:37
i just give idea but i have not know it is possible or not – Harshil Kotecha Apr 25 '17 at 13:38
`datetime('now','localtime')` worked fine for me. It gives you the date and time of your timezone. – Filipe Martins Apr 25 '17 at 13:45
No problem I get time local timestamp from iOS Swift and add in this query – Harshil Kotecha Apr 25 '17 at 13:54
TimeZone is not fix. Because this app for all Asia . So need system mobile timezone (local) – Harshil Kotecha Apr 25 '17 at 14:00
problem is here if my current time is 12:46 1st record is 01:00 i don't want next event i wan't running event and than after next to next event – Harshil Kotecha Apr 26 '17 at 05:14
you near by my answer – Harshil Kotecha Apr 26 '17 at 05:23

score 0 · Answer 2 · answered Apr 25 '17 at 13:01

0

you can ORDER BY by expression so just wrap your logic in simple expression using CASE, date time functions and you're good to go

answered Apr 25 '17 at 13:01

nimdil

1,361
10
20

Please give query – Harshil Kotecha Apr 25 '17 at 13:01
yes you near by the answer but i haven't knowledge about SQL so can't understand your theory is write but i wan't query . (by the way thank you ) – Harshil Kotecha Apr 25 '17 at 13:41

order by Time using current time?

2 Answers2