Cannot compare timestamps that have been cast from epoch format

Philip Drew shared this question 2 years ago
Answered

I'm creating a calculating field that compares a timestamp field that has been cast from unix epoch format, to another field already in the timestamp format. I'm casting the epoch (stored as text) as a number and then use the built in java converter which completes the cast to a timestamp. It shows up in the right format, but when performing any comparison between the fields mentioned previously, I get the error:


ERROR: operator does not exist: text = timestamp without time zone Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 82


Unsure how to proceed, please advise

Comments (3)

photo
1

Hi Philip,


Do you have a few minutes to screen-share this afternoon so that I can see how you are setting this up?f so, please let me know what time works best for you. I am on United States Mountain Time.


Regards,

Nathan

photo
1

Hi Philip,

I just notice that your email is registered to the UK, if you have time on your Monday afternoon please let me know. One idea in the meantime is to do this entirely using Freehand SQL. I will need to see how you have this setup to provide specifics, but most DBMSes include a function to convert from epoch to to a date field such as MySQL's:


  1. from_unixtime(13894226)

You can then do any necessary casts within this SQL statement as well.Please let me know if this works for you, or if you would like to meet up and discuss specifics.

Regards,

Nathan

photo
1

Hi Philip,


I am going to set this ticket to closed for now, but if there is anything I can do to help here, or if you are still facing problems, please just let me know and the case will be re-opened!


Regards,

Nathan