0

I am consuming an Oracle APEX REST web service using HTML5. Some records are retrived from the server and stored locally using web sql. Local table has the same structure as the server db table, including a DATETIME column. 

Example data:
01   10-03-2015 12:00  (March 10th, 2015)
02   01-04-2015 13:45  (April first, 2015)
03   03-05-2015 20:00  (May 3rd, 2015)

I have trouble with local storage (web sql) because when I order all table records by that datetime column, it is ordering the records following a text based order (from left to right): DD-MM-YYYY HH:MI

The code I am using in HTML5 to get these records ordered is:

select * 
from local_table
order by fecha

Wrongly I am getting this:

02   01-04-2015 13:45  (April first, 2015)
03   03-05-2015 20:00  (May 3rd, 2015)
01   10-03-2015 12:00  (March 10th, 2015)

When you check this records directly in Oracle DB without specifying a format, DATES are stored DD-MM-YYYY HH:MI. Oracle is not having trouble ordering those records by DATE because when I order them in ORACLE i am getting the right order:

01   10-03-2015 12:00  (March 10th, 2015)
02   01-04-2015 13:45  (April first, 2015)
03   03-05-2015 20:00  (May 3rd, 2015)

Do I have to order date value just before I store it locally in order to have it stored in YYYY-MM-DD HH:MI so I can have them correctly ordered?

I saw another post where somebody suggest that, but I wonder if that is a workaround or the only way to do it.

Thanks

user34941
  • 11
  • 3

1 Answers1

0

WebSQL uses SQLite under the hood, so your question is answered here: SQLite Order By Date

You basically need to convert column values with date() function when ordering.

Community
  • 1
  • 1
M Kowalski
  • 460
  • 4
  • 6