Other [Solved] Convert ticks to date

Discussion in 'Android Questions' started by makis_best, Jul 3, 2018.

  1. makis_best

    makis_best Active Member Licensed User

    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....

    [​IMG]

    Thank you.
     
  2. stevel05

    stevel05 Expert Licensed User

    Something to do with this maybe?

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

     
  3. makis_best

    makis_best Active Member Licensed User

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

    stevel05 Expert Licensed User

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

    makis_best Active Member Licensed User

    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.
     
    Last edited: Jul 3, 2018
  6. stevel05

    stevel05 Expert Licensed User

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

    makis_best Active Member Licensed User

    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?
     
    Last edited: Jul 3, 2018
  8. stevel05

    stevel05 Expert Licensed User

    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.
     
  9. makis_best

    makis_best Active Member Licensed User

    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?
     
  10. stevel05

    stevel05 Expert Licensed User

    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.
     
  11. makis_best

    makis_best Active Member Licensed User

    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)/
     
  12. stevel05

    stevel05 Expert Licensed User

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

    makis_best Active Member Licensed User

    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?
     
  14. stevel05

    stevel05 Expert Licensed User

    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.
     
  15. makis_best

    makis_best Active Member Licensed User

    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'
     
  16. Mahares

    Mahares Well Known Member Licensed User

    Code:
    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:
    Code:
    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)
     
    LucaMs and mangojack like this.
  17. makis_best

    makis_best Active Member Licensed User

    Yes....

    Thank you Mahares that's it...

    Thank you.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice