Android Question [B4X] How to convert timestamptz from a postgresql database to ticks

[Alexander Stolte]

I get the following date and time back from my postgresql database: 2023-08-22T14:41:15.569105992Z

how can i convert this to ticks so i can use this in B4X?

 

[Sandman]

Considering that you posted this in the Android part of the forum, is it fair to assume that you can only do the conversion on the Android side? Or do you actually have the option to modify the sql that generated the result?

If it's the latter, then perhaps you can find some clue in this thread:

Convert date to unix timestamp in postgresql

I have a table with a column abc carrying the unix timestamp (eg. 13898161481435) and I want to run a between dates select. It would be not efficient to do a where TO_CHAR(TO_TIMESTAMP(abc / 1000...

stackoverflow.com

 

[aeric]

Can you discard the nanoseconds?

Dim timestamp As String = "2023-08-22T14:41:15.569105992Z"
timestamp = timestamp.Replace("T", " ").Replace("Z", "")
timestamp = timestamp.SubString2(0, 23)
Log(timestamp)
DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss.SSS"
Dim value As Long = DateTime.DateParse(timestamp)
Log(value)
Log(DateTime.Date(value))

 

[Alexander Stolte]

Considering that you posted this in the Android part of the forum, is it fair to assume that you can only do the conversion on the Android side? Or do you actually have the option to modify the sql that generated the result?

I edited the title to be more specific, thanks.

Can you discard the nanoseconds?

I would like to leave everything by default. I am currently working on a Supabase class and this is what the API gives me back by default.
I'll try, thanks

 

[aeric]

2023-08-22T14:41:15.569105992Z

From my search on PostgreSQL Timestamp with Timezone, the format should be like
2023-08-22 14:41:15.569105+08
where +08 is the timezone

edit: My finding is Supabase timestamp format is like 2023-08-22 14:41:15.569105+00:00

 

[Mahares]

I don't think that format is limited to Postgre. It is common. The T is a place holder for a space:

Dim s As String = "2023-08-22T14:41:15.569105992Z"
    s=s.Replace("T"," ")  
    DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSSSSS"
    Log(DateTime.DateParse(s))   '1692729675569

 

[aeric]

I don't think that format is limited to Postgre. It is common. The T is a place holder for a space:

Dim s As String = "2023-08-22T14:41:15.569105992Z"
    s=s.Replace("T"," ")  
    DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSSSSS"
    Log(DateTime.DateParse(s))   '1692729675569

It works in B4A but not in B4J.

 

[Mahares]

Dim s As String = "2023-08-22T14:41:15.569105992Z"
    s=s.Replace("T"," ")                  
    #if B4J
        s=s.SubString2(0, s.LastIndexOf(".")+4) 
        s=$"${s}Z"$    
    #End If
    DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss.SSSSSSSSS"
    Log(DateTime.DateParse(s))   '1692729675569

The last 6 digits of the string (105992) do not impact the result anyway.