Other [Solved] Convert ticks to date

[makis_best]

Hi

I read some dates from SQL Server.... and I save them to my SQLite database.
The dates stored like that: /Date(1498856400000)/.
Every record has a value like that.

It is a correct format? I need to change it some how?

After that I try to convert that value in my SQLite query but no luck.

How I can convert that value to a real date on my SQLite query?

Until now I try....

Thank you.

 

[stevel05]

Something to do with this maybe?

From SQLite document found here: https://www.sqlite.org/lang_datefunc.html

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167).

 

[makis_best]

I really don't understand what you try to tell me...
The stored format is wrong?

 

[stevel05]

The calculation : 1525726800000 / 100000000 - 62135596800 returns -6.2135581542732E10 is this what you expect?

 

[makis_best]

The calculation : 1525726800000 / 100000000 - 62135596800 returns -6.2135581542732E10 is this what you expect?

No... it is wrong.
If i calculate correct it must be 636345854570000000

The problem is that the 1525726800000 is not correct...
The correct value as I calculate it must be 1499040000.
The value 1499040000 returns 03/07/2017 witch is correct. (dd/mm/yyyy format)
That what I understand....
I believe that the stored format of my SQLite field is not correct.

 

[stevel05]

Sorry, my calculation was wrong one to many 0's in the divisor, but the result is still -62135444227

 

[makis_best]

Sorry, my calculation was wrong one to many 0's in the divisor, but the result is still -62135444227

I agree with you... your calculation is correct.
But from SQL Server I read a date in format yyyy-mm-dd and the Web service return a value like {"Date1":"\/Date(1498856400000)\/"}.
If I convert that value to date gives 11/14/49466.... Not correct. The correct is 2017-07-01 00:00:00 so I believe that the read value is not correct.

What format must have the date I read from SQL Server?

 

[stevel05]

I can't test the conversion in SQLite, but in B4j DateTime.Date(1498856400000) using DateTime.DateFormat = "dd/MM/yyyy HH:mm:ss" gives 30/06/2017 22:00:00. The difference could be down to time zone, I'm in the UK. So it appears that you may not need to do any further manipulation before converting the date.

 

[makis_best]

I am in Greece and when I execute
DateTime.DateFormat = "dd/MM/yyyy HH:mm:ss"
Log(DateTime.Date(1498856400000))
I get 01/07/2017 00:00:00 --> 01/07/2017 00:00:00 it is OK!!!

So... how I can get the date 01/07/2017 00:00:00 from SQLite script not b4a code?

 

[stevel05]

First try just removing the additional calculations (/ 100000000 - 62135596800) and see what it gives you.

If that doesn't work, post the query as a String and I'll play with it here and try to find out why it's not working (I'll need to read up on the SQLite conversion). But I have to go out now for a few hours, so it won't be until I get back unless someone else can help you before then.

 

[makis_best]

No problem.... I remove the calculations

I post my sqllite script here.

SELECT RegistrationDate AS 'Date1' FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd'
And I get
/Date(1499288400000)/
/Date(1506373200000)/
/Date(1509660000000)/
/Date(1509660000000)/
/Date(1516312800000)/
/Date(1522789200000)/
/Date(1525381200000)/
/Date(1528405200000)/

 

[stevel05]

You will still need to extract the date value using Substr and include the conversions in the query (as in your original post)

 

[makis_best]

You will still need to extract the date value using Substr and include the conversions in the query (as in your original post)

Done
SELECT substr(RegistrationDate, 7, 13) AS 'Date1' FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd'
And I get....
1499288400000
1506373200000
1509660000000
1509660000000
1516312800000
1522789200000
1525381200000
1528405200000

But still missing the conversion...
How to do it?

 

[stevel05]

Why do you need to convert the date in SQLite?, although I am sure it's possible, it would make more sense to convert to a date when you display it in the app.

 

[makis_best]

Save lot of coding cuz the only thing I want to do is to display 8 dates plus names of the record to a webview nothing more.
The list is ready. The only thin to be done is the conversion.
All the script is....
SELECT substr(RegistrationDate, 7, 13) AS 'Date1', Names AS 'The name' FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd'

 

[Mahares]

The only thin to be done is the conversion.
All the script is....

MyQuery="SELECT  strftime('%d/%m/%Y %H:%M:%S', substr(RegistrationDate, 7, 13)/1000, 'unixepoch','utc')  AS Date1 " _
    & " FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd' "
    Cursor1=SQL1.ExecQuery(MyQuery)

and if you do not want the time component, use this:

MyQuery="SELECT  strftime('%d/%m/%Y', substr(RegistrationDate, 7, 13)/1000, 'unixepoch','utc')  AS Date1 " _
    & " FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd' "
    Cursor1=SQL1.ExecQuery(MyQuery)

 

[makis_best]

MyQuery="SELECT  strftime('%d/%m/%Y %H:%M:%S', substr(RegistrationDate, 7, 13)/1000, 'unixepoch','utc')  AS Date1 " _
    & " FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd' "
    Cursor1=SQL1.ExecQuery(MyQuery)

and if you do not want the time component, use this:

MyQuery="SELECT  strftime('%d/%m/%Y', substr(RegistrationDate, 7, 13)/1000, 'unixepoch','utc')  AS Date1 " _
    & " FROM EGM_Kartela_Pelati WHERE GID = 'c500fc20-3ed0-4742-8bc6-4f8fff0b77cd' "
    Cursor1=SQL1.ExecQuery(MyQuery)

Yes....

Thank you Mahares that's it...

Thank you.