cancel
Showing results for 
Search instead for 
Did you mean: 

What is the most efficient way to convert TIMESTAMP to BIGDATETIME?

Former Member
0 Kudos

I'll probably fumble my way into a solution here (because I have none at the moment) but hoping someone chimes

in with their favorite way to do this.

Thanks,

Michael

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

I have a couple of ways to do it:

CREATE INPUT WINDOW INWINDOW SCHEMA (
C_Integer integer,
C_Bigdatetime bigdatetime,
C_Timestamp timestamp,
C_Date date,
C_Long long,
C_String string,
C_Money2 money(2),
C_Money7 money(7),
C_Binary binary,
C_Float float)
PRIMARY KEY ( C_Integer ) ;


CREATE OUTPUT WINDOW OUTWINDOW SCHEMA (
Column1 integer ,
Column2 bigdatetime ,
Column3 bigdatetime ) PRIMARY KEY ( Column1 ) AS
SELECT
  IW.C_Integer as Column1,
  cast(bigdatetime,IW.C_Timestamp) as Column2,
  to_bigdatetime(to_long(IW.C_Timestamp)*1000) as Column3 FROM INWINDOW IW ;

You can test it with esp_subscribe:

%ESP_HOME%\bin\esp_subscribe -c studio:studio -p localhost:9786/default/p6 -s INWINDOW,OUTWINDOW

<INWINDOW ESP_OPS="i"  C_Integer="500" C_Bigdatetime="2014-05-10 04:10:26.609123" C_Timestamp="2014-05-10 04:10:26.609" C_Date="1970-01-01 19:12:12" C_Long="76" C_String="string" C_Money2="2.22" C_Money7="7.7777777" C_Binary="42696E6172792064617461" C_Float="3.141590"/>

<OUTWINDOW ESP_OPS="i"  Column1="500" Column2="2014-05-10 04:10:26.609000" Column3="2014-05-10 04:10:26.609000"/>

Thanks,

  Neal

Former Member
0 Kudos

Perfect.  Thanks Neal.

I've gone with this approach :

to_bigdatetime(to_long(a.timestamp_column)*1000)


and wrapped it with totimezone to move my implied EST timestamps into true GMT bigdatetime values.


totimezone(to_bigdatetime(to_long(a.timestamp_column)*1000),'EST','GMT')


This is working well for me.


Greatly appreciated.


Michael

Answers (0)