Read date/time in an sqlite data base file

[ssimop]

I read in an sqlite data base file the supposed to be a filetime-type field, the string : "2459067.10928241". I am in need of any advice on how to covert it into to two parts, namely date (dd-mm-yyyy) and time (hh:mms:...). Methods already used in QM do not work in this case. Is it likely that I need any other functions? Many thanks in advance.

[Gintaras]

What date-time format it is?

[Kevin]

I could be wrong but it appears that its Julian Date
I happen to have a c# conversion function for that. if it's not ignore this post.
if it is Julian date try this

Code:

Copy      Help

str s="2459067.10928241"
str dt
dt=sub.JulianDateToDateTimeString(s)
out dt
DateTime dt1.FromStr(dt)
out dt.timeformat("{dd-MM-yyyy}" dt1)
out dt.timeformat("{hh:mm:ss}" dt1)

#sub JulianDateToDateTimeString
function~ ~jds
DECIMAL jd
jd=jds
CsScript x
x.AddCode("")
str R=x.Call("JulianToDateTime" jd)
ret R



#ret
//C# code
using System;

public class Class1
{
,public static DateTime JulianToDateTime(double julianDate)
,{
,,double unixTime = (julianDate - 2440587.5) * 86400;
,,DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
,,dtDateTime = dtDateTime.AddSeconds(unixTime).ToLocalTime();
,,return dtDateTime;
,}
}

with this function 2459067.10928241
converts to 8/5/2020 10:37:22 AM on my pc

[Gintaras]

Also try SQLite functions in your SQL string.

https://www.sqlite.org/lang_datefunc.html

https://www.sqlitetutorial.net/sqlite-date/

 

Code:

Copy      Help

SELECT date(d1), time(d1) FROM datetime_real;

[ssimop]

Dear Kevin,

Thank you so much! Actually it is Gregorian (Julian Date) and your routine worked absolutely perfectly! Best Regards!

---

Dear Gintaras,

Many thanks for your reply too. Since I am not familiar with Sqlite, I would appreciate your assistance in getting this date/time information. I read the database using :
Function sqllt2

Code:

Copy      Help

Sqlite db3.Open(dbfile)
ARRAY(str) ar; int r
db3.Exec("SELECT * FROM mydataf" ar)
for r 0 ar.len ;;for each row
,out "%-10s %s %s %s %s %s %s %s %s %s %s" ar[0 r] ar[1 r] ar[2 r] ar[3 r] ar[4 r] ar[8 r] ar[9 r] ar[10 r] ar[10 r]

The date/time real information is in element ar[10 r] and the string contained is "2459067.10928241".

Will you please use this example with the SELECT statement that you suggested?

Many thanks!

[ssimop]

Dear Gintaras,

Many thanks for your reply too. Since I am not familiar with Sqlite, I would appreciate your assistance in getting this date/time information. I read the database using :
Function sqllt2Code: Copy      Help

Code:

Copy      Help

Sqlite db3.Open(dbfile)
ARRAY(str) ar; int r
db3.Exec("SELECT * FROM mydataf" ar)
for r 0 ar.len ;;for each row
,out "%-10s %s %s %s %s %s %s %s %s %s %s" ar[0 r] ar[1 r] ar[2 r] ar[3 r] ar[4 r] ar[8 r] ar[9 r] ar[10 r] ar[10 r]

The date/time real information is in element ar[10 r] and the string contained is "2459067.10928241".

Will you please use this example with the SELECT statement that you suggested?

Many thanks!

---

OK, I am happy that the following worked perfectly too!

db3.Exec("SELECT date(rec), time(rec) FROM tbx" ar)

Best regards!

[ssimop]

Dear Gintaras, I have just received an e-mail from you referring toMacro Macro3016

but - I am afraid - I cannot find/read this post in the forum. Please advise.

[Gintaras]

After posting I noticed

OK, I am happy that the following worked perfectly too!

db3.Exec("SELECT date(rec), time(rec) FROM tbx" ar)

and deleted the post. It was similar code. Not tested, I don't know is it correct.

Macro Macro3016

Code:

Copy      Help

Sqlite db3.Open(dbfile)
ARRAY(str) ar; int r
db3.Exec("SELECT date(dateColumnName), time(dateColumnName) FROM mydataf" ar)
for r 0 ar.len ;;for each row
,out "%s %s" ar[0 r] ar[1 r]