ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  C# DBTable data from Sqlite
Posted by Drops2 on May-16-2020 00:33
The trouble is that sqlite has't type DateTime[] therefore I use type sting[]  in sqlite. My string example  "05142018 00:00"

Advise me how to get data from sqlite to timeStamps DBTable pls.

DBTable table = new DBTable(sqlite_cmd.ExecuteReader());
//timeStamps = table.getColAsDateTime(0); //impossible other type not DateTime
string[] MyString = table.getColAsString(0);
openData = table.getCol(1);
highData = table.getCol(2);
lowData = table.getCol(3);
closeData = table.getCol(4);
volData = table.getCol(5);

  Re: C# DBTable data from Sqlite
Posted by Peter Kwan on May-16-2020 15:49
Hi Drops,

The result of a database query depends on the database schema, how you enter the data to the database, the database driver used, how the SQL query is written, and other factors such as the configuration of your database driver and the locale settings of your computer.

For your case, it seems the format is mmddyyyy hh:nn (mm = month, dd = day of month, yyyy = year, hh = hour, nn = minute). I assume you have used a query which returns text strings in the above format.

As everyone can choose a different format, you would need to write code to convert the text string to a standard format (eg. DateTime) so that other code can understand it. For your format, the code can be something like:

DateTime[] timeStamps = new DateTime[MyString.Length];
for (int i = 0; i < MyString.Length; ++i)
    string s = MyString[i];

    // The following is how your text string is to be interpreted
    int mm = int.Parse(s.Substring(0, 2));
    int dd = int.Parse(s.Substring(2, 2));
    int yyyy = int.Parse(s.Substring(4, 4));
    int hh = int.Parse(s.Substring(9, 2));
    int nn = int.Parse(s.Substring(12, 2));
    int ss = 0;

    timeStamps[i] = new DateTime(yyyy, mm, dd, hh, nn, ss);

Hope this can help.

Peter Kwan

  Re: C# DBTable data from Sqlite
Posted by Drops2 on May-17-2020 23:52
Thank you Peter for reply. Your code works great. I think about solusion for big financial data base. Your function for converting string to DateTime takes some time. Maybe it will be better and faster for solution to change sqlite on the server database for example mysql, it conteins type DateTime or to making a file database with serialization ?

  Re: C# DBTable data from Sqlite
Posted by Peter Kwan on May-18-2020 20:14
Hi Drops2,

The conversion code should take insignificant time when compared to the time you use to get the data from the database.

For example, suppose your chart has 100000000 date/time values. You may think it takes some time to convert the strings to DateTime. In practice, the time it takes is very small when compared to the time it takes to read 100000000 records from the database.

If you are worry about efficiency for big data, you are correct that the most important thing is to optimize the database. You can choose a more efficient format such as as an integer representing seconds elapsed since epoch (sometime also called UNIX timestamps). Apart from smaller and faster, the integer format is sortable and therefore searchable, that is you can sort the records by date/time and can find records between certain date/time range. If you use UNIX timestamps, the Chart.chartTime2 API can be used to convert the UNIX timestamps to .NET DateTime.

(The mySQL DateTime is completed different from .NET DateTime. mySQL contains a built-in function UNIX_TimeStamp to convert the mySQL DateTime to UNIX timestamps, and this is one of the most often used method to get the date/time from mySQL to a programming language.)

Your original string format is not sortable. If you shot in ascending order, the date 04142019 00:00 will appear earlier than 05142018 00:00, even though we know that the year 2019 cannot appear earlier than 2018. If the dates are not sortable, you cannot create a query to obtain records between any two dates.

Note that in any case, there must be conversions. It is because the sqlite uses a certain format, and the .NET language uses another format. So somewhere there must be conversions. As explained above, the conversions takes insignificant time compared to the time to get the records, so normally we do not need to worry about them.

Peter Kwan

  Re: C# DBTable data from Sqlite
Posted by Drops2 on May-19-2020 03:05
You are absolutely right Peter. Simple solutions are the best. I will stay with sqlite and type UNIX timestamps. Thank you for your answer.