How can we help?

New post
0

Issues with converting dates from string to EPOCH

Avatar
Carlos Kynäslahti
None
Follow

I have an IF function which compares two dates. I noticed it acts weird when I tried to use regular strings from my data source, so I need to convert them to EPOCH. 

In reality I use a SELECT statement to get the correct timestamp from my data source, but for the sake of this post, I wrote it out as a date:

 

DATE("21-Nov-2017 00:02:01","dd-MMM-yyyy HH:mm:ss")

 

Instead of the EPOCH time, I get a blank result. I can't figure out what's wrong with my script, I tried playing around with the [format] argument but nothing worked.

 

What am I doing wrong?

 

 

 

Date Votes

8 comments

  • 0
    Avatar
    Meggan King

    Hi Carlos -

    The example you have is correct format for converting date. If your data source is failing, it could have another character in the data. One trick we use is to wrap URLENCODE around the data to see if any unexpected characters show up. Can you try that? The support team would also be happy to investigate if you would like to have us go in and check your klip? You can email us at support@klipfolio.com to open a ticket

    Thanks,

    Meggan

  • 0
    Avatar
    Carlos Kynäslahti

    Hi Meggan,

     

    1. What I failed to mention in my initial post is that also the example I pasted here fails, not just the data source. A simple conversion from a string format timestamp gives me a blank result.

    Screenshot:

     

    2. The URLENCODE function gives me a result of "22-Nov-2017+00%3A15%3A53" (this is a more recent timestamp from last night). I assume it's not supposed to have those encoding characters? How do I fix this?

    Screenshot:

     

    Thanks,

    Carlos

  • 0
    Avatar
    Meggan King

    Hi Carlos -

    Your string is fine. If you try to evaluate it, does it work?

     

    What are the properties set to in your screenshot? There might be something strange happening there. Here is an example of what it could be using Date/Time format

     

    Thanks,

    Meggan

  • 0
    Avatar
    Carlos Kynäslahti

    1. Evaluation results are blank:

     

    2. "Format as:" was set to automatic (text), but changing it to "Date / Time" has no effect:

     

    Thanks,

    Carlos

  • 0
    Avatar
    Meggan King

    Hi Carlos -

    Thanks for the screenshots, that is definitely unexpected. I'm going to open this as a ticket with the support team to investigate. You'll have an email from the team asking for a few more details. 

     

    Thank you,

    Meggan

  • 0
    Avatar
    Carlos Kynäslahti

    Thank you for your answers!

     

    - Carlos

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Carlos,


    It looks like the reason this is happening is that your date locale is set to Finnish.
    This means that the DATE function will be expecting the dates in finish language, so instead of Nov, it would be expecting marras.
    So unfortunately, we cannot convert directly using that date format, and the DATE functions, since the data coming in is in English.

    However, there is a workaround to this, since the Properties panel Date/Time format assumes all dates are in English regardless of date format.

    Here is an example of how this can be done;
    -First, take the dates in their raw English format, and convert them to a format of only numbers, so that the language being used is irrelevant.


    -Then, in another column, do the following, since the dates are now in a format where language does not matter, and convert them to unix.


    This is done via a results reference.

    Hope this helps!


    Best Regards,
    Joshua
    _____________________________
    Joshua Cohen-Collier
    Technical Support Engineer, Klipfolio

  • 0
    Avatar
    Carlos Kynäslahti

    Hi Joshua,

    Thanks again for your help. That workaround does indeed work but there's something that doesn't make sense.

    I checked my data source and I'm getting the results in as "dd-MMM-yyyy HH:mm:ss" (eg, 27-Nov-2017 12:09:49) but if I run the same SQL script locally, all my dates are using the format "dd-MM-yyyy HH:mm:ss" (eg. 27-11-2017 12:09:49). Why does Klipfolio convert the month to MMM instead using the original MM-format? Can I disable it somehow? 

     

    Thanks,

    Carlos

Please sign in to leave a comment.