Derived Property function to convert string to number

  • 18 January 2022
  • 5 replies


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.




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? 


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:

Userlevel 1

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: Can you share your feedback here?