Solved

Derived Property function to convert string to number

  • 18 January 2022
  • 5 replies
  • 401 views

Badge

Hi all,

I have user property with timestamp in `1619000000.571` format. That’s UNIX timestamp with millis as a float value.

I want to put that value in LONG_TO_TIME, but it doesn’t accept it in that format.

Tried SPLIT to remove 2nd part of timestamp, but SPLIT returns String, and LONG_TO_DATE doesn’t work with Strings. Which options do I have?

  1. Would be great to have STRING_TO_NUMBER function.
  2. Another option: mprove LONG_TO_DATE to support timestamps like that.

Thanks,

Ostap

icon

Best answer by belinda.chiu 20 January 2022, 01:10

View original

5 replies

Userlevel 6
Badge +8

Hi @ostap ! Welcome to the Amplitude Community :wave:  I’m happy to help! 

I checked internally and found that you can get around this by doing DATE_TIME_FORMATTER (PROPERTY , "s","yyyy/MM/dd").

This help doc page describes how to use the DATE_TIME_FORMATTER function. In this case, I am telling the formatter we are getting a value in seconds (“s”), and I want a return format like 2021/04/21 (“yyyy/MM/dd”).

If you want to output to look like the LONG_TO_TIME function, then this “YYYY-MM-dd HH:mm:ss” should be the output format.

Can you try the above and let me know how that works for you? 

Badge

Thank you, Belinda. It worked as expected!

Didn’t know about that `”s”` functionality in SimpleDateFormat. Thanks for sharing that internal workaround.

Userlevel 6
Badge +8

@ostap Glad to hear that it worked and you’re very welcome! :)

Our Derived Properties doc does have a handy link to the SimpleDateFormat functionality list. I’m going to share it here in case other Community members find it useful: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Userlevel 1
Badge

I agree with OP that a STRING_TO_NUMBER function would be useful.

Im my case I want to use "split" to pull a value from a longer string, then use math functions on it. 

Userlevel 6
Badge +8

Hi @Jacobt ! Thanks for your suggestion and I think this would be a great add to our Product Ideas page: https://community.amplitude.com/ideas Can you share your feedback here? 

Reply