I have two “datetime” fields that were imported to PanoramaX from csv file as text fields:
The first field has dd.mm.yy hh:mm format: 07.04.17 06:40
The second field has yyyy-mm-dd hh:mm:ss +GMT format: 2017-04-07 06:40:39 +0000
Please advise how could I convert both fields into superdate since I need to calculate the difference in minutes.
Thanks.
Andrej
You use text funnels to extra the appropriate strings of text for date and time. The date( and time( functions convert the text to regular dates and time and the superdate( function converts their results into superdates.
Presuming your cvs file reliably includes any leading zeros, as in your example, for the ‘First’ field (replace 'First with your actual filename):
superdate(date(First[1,8]),time(First[10,14]))
and for the ‘Second’:
superdate(date(Second[1,10]),time(Second[12,19]))
Panorama Superdates don’t deal deal with GMT offsets or time zone differences on their own. Superdate(today(),now()) would give a local superdate value for whatever time zone that computer had set in its system preferences. So you’ll have to add or subtract manually (superdate’s units are seconds, so 3600/hour) to normalize a time to GMT. Your first field doesn’t specify its GMT offset, so I presume that value is fixed and known to you. Second[21,25] would be the text funnel to use to extract the offset, including the ‘+’ sign from the second field, or Second[22,25] if you don’t want the ‘+’. Use that do determine how much to add or subtract to normalize your times.
Often there’s more than one way to write what you need in a text funnel. They are an incredibly useful feature of Panorama, especially powerful when multiple funnels are combined. They include some advanced options, but a lot can be done quite simply. Panorama X’s Morph dialog will let you do some of this without understanding text funnels, but I’d encourage anyone new to Panorama X to read the Help page on Text Funnels.
Here is a slightly different approach:
If we call your two formats Time1 and Time2, the functions,
superdate(date(Time1[1," "]),time(Time1[" ",-1]))
and
superdate(date(Time2[1," "]),time(array(Time2,2," ")))
will work. The difference may be negative as shown below:
Here’s another alternative that doesn’t use a text funnel. I think it might be a bit easier to understand. I’ll use Michaels field names. For the first format:
superdate(date(firstword(Time1)),time(lastword(Time1)))
For the second format:
superdate(date(firstword(Time2),time(nthword(Time2,2)))That was really helpfull, thank you!