PureEngage On-Premises

 View Only

Sign Up

Back to discussions
Expand all | Collapse all

How do you convert an infomart timestamp like mediation_segment_fact.start_ts to an actual 'dd-mm-yyyy hh:mi:ss' date field?

  • 1.  How do you convert an infomart timestamp like mediation_segment_fact.start_ts to an actual 'dd-mm-yyyy hh:mi:ss' date field?

    Posted 03-06-2017 23:34


  • 2.  RE: How do you convert an infomart timestamp like mediation_segment_fact.start_ts to an actual 'dd-mm-yyyy hh:mi:ss' date field?

    Posted 03-07-2017 05:11
    What is your database platform. For Oracle the syntax is to_date('1970-01-01', 'YYYY-MM-DD') + [field name]/86400.  The filed name in this case would be START_TS.  In the MSF GI2 view the timestamp has already been converted to the date time.  And be wary of any UTC conditions on your server.  You may need to adjust for DST.

    I'm not sure what the conversion would be for other DB's but a search on google using temrs such as convert unix time stamp to datetime should give you a place to start.


  • 3.  RE: How do you convert an infomart timestamp like mediation_segment_fact.start_ts to an actual 'dd-mm-yyyy hh:mi:ss' date field?

    Posted 03-07-2017 15:21
    Hi Nikolas, it's Oracle DB and this worked like a charm.  I also found a date/time field start_ts_time in the mediation_segment_fact_gi2 table you mentioned which provided what I needed today but the conversion you provided will help me for years to come.  Many thanks, Jay