Help Required for date format conversion

[ABP2021]

Could you please help me with converting a date from

Format 20250731T233347.455+1000 

to

Format 2024-11-20 19:38:02
If you know the steps or QV logic to achieve this, kindly let me know.

Appreciate your help in advance!

[Oleg_Troyansky]

Hi there,

I'm not familiar with the first format to the fullest, but it looks like it starts from the date YYYYMMDD, then the letter T, and then the time hhmmss. After that, I can only presume that 455 represent milliseconds, and I'm not sure what +1000 stands for.

So, assuming that you only need your timestamps to be stored up to the second level, I'd do something like this:

date(

     date#( left(MyDate, 15)    // only extract the first 15 symbols

              , 'YYYYMMDDThhmmss')   // this is the source date/time format

     , 'YYYY-MM-DD hhmmss)   // this is the target date/time format

Something like this should work. I never tried converting timestamps with the letter T in them, though. So, if this doesn't work, then you might have to parse the string

Cheers,

Oleg Troyansky

[ABP2021]

Hi Troyansky,

Thanks for your reply. basically, after version upgrade the date format of log is changed. that we wanted to update like earlier.

[Kaushik2020 · Accepted solution]

you can try to format it via load editor may be with something like below. 

// Sample inline data
Temp:
LOAD * INLINE [
    RawTimestamp
    20250731T233347.455+1000
];

// Process the timestamp
Final:
LOAD
  RawTimestamp,

// Extract parts
  Date(Mid(RawTimestamp, 1, 8), 'YYYYMMDD') as DatePart,
  Time#(Mid(RawTimestamp, 10, 6), 'hhmmss') as TimePart,
  Left(Mid(RawTimestamp, 17, 3), 3) as MilliSec,
  Mid(RawTimestamp, 20, 5) as TimezoneOffset,

// Convert to UTC datetime (adjusting for timezone)
  Timestamp(
    Timestamp#(Mid(RawTimestamp, 1, 😎 & ' ' & Mid(RawTimestamp, 10, 6), 'YYYYMMDD hhmmss')
    - (Num(Mid(RawTimestamp, 20, 3)) / 24)
    ) as UTC_Timestamp,

// Format in final required format
  Date(Date#(Mid(RawTimestamp, 1, 8), 'YYYYMMDD')) & ' ' &
  Time(
  Timestamp(
  Timestamp#(Mid(RawTimestamp, 1, 😎 & ' ' & Mid(RawTimestamp, 10, 6), 'YYYYMMDD hhmmss')
   -  (Num(Mid(RawTimestamp, 20, 3)) / 24)
   ), 'hh:mm:ss'
  ) as FinalFormattedTimestamp

Resident Temp;
DROP TABLE Temp;

Pls convert these emoji to 8 ) as mentioned in the below image. Seems it is auto changing this. see below image with highlighted text.

[antose]

tmp:
LOAD * INLINE [
RawTimestamp
20250731T233347.455+1000
];

NoConcatenate
Final:
LOAD RawTimestamp,
timestamp(Timestamp(Timestamp#(Replace(Left(RawTimestamp,15),'T',' '), 'YYYYMMDD hhmmss')),'YYYY-MM-DD hh:mm:ss') AS FinalTimestamp
RESIDENT tmp;

DROP TABLE tmp;

// FinalTimestamp = 2025-07-31 23:33:47