3

I am using Taffy framework for creating api endpoints. In one of my endpoint I need to display time in particular format say hh:mm:ss, I used timeformat method in CFC file to get the desired result, but when I hit the endpoint I am getting January, 01 1970 17:00:00. Any suggestions?

Note:For field "clientHoursOfOperationOpenTime" Time value is returned from Mysql, I am querying the DB in CFC file and formatting the clientHoursOfOperationOpenTimevalue.

<cfset var getLocationHours = QueryNew('') >
<cfquery name="getLocationHours" datasource="#appSettings.DSN#">
Select some fields here
</cfquery>

<cfset getLocationHours.clientHoursOfOperationOpenTime= "#timeFormat(getLocationHours.clientHoursOfOperationOpenTime, 'hh:mm:ss')#">
 <cfreturn getLocationHours >

When I dump this field getLocationHours.clientHoursOfOperationOpenTime I am getting the formatted value. But when I use taffy, I am getting the format issue.

Shawn
  • 4,758
  • 1
  • 20
  • 29
Kishore Prabhakar
  • 41
  • 6
  • 2
    Show some code? – TRose Feb 06 '18 at 15:55
  • What version of ColdFusion? – Shawn Feb 06 '18 at 16:30
  • You might doing something fundamentally wrong. You are selecting a datetime field, `field "clientHoursOfOperationOpenTime" DateTime value is returned from MySQL` and stripping away the date portion by using timeformat(). If that's what you really want to do, bear in mind that timeformat() returns a string. Make sure taffy expects a string. – Dan Bracuk Feb 06 '18 at 16:31
  • "1/1/1970" is likely your epoch date, so it appears that you may be getting a default date with your time. What is the datatype of the field your are querying? Also, since ColdFusion 10, "m" is deprecated for "minutes"; use "n" instead. – Shawn Feb 06 '18 at 16:35
  • We are using "time" datatype in mysql, we also tried "n" instead of "m". We are using coldfusion 11 – Kishore Prabhakar Feb 06 '18 at 16:45
  • When you set `getLocationHours.clientHoursOfOperationOpenTime` using the `timeFormat` function... does it need the quotes? If it's wrapped in quotes aren't you saving it as a text string? – TRose Feb 06 '18 at 16:57
  • Also, "hh" is a mask for a 12-hour clock with leading zero; You probably want "HH". For your 5pm example, it will return "05:00:00". Date and time masking can be fun. Especially in a "case-insensitive" language that switches the meaning of masking characters depending on the (sometimes case-sensitive) formatting function used. – Shawn Feb 06 '18 at 17:02
  • `TIME` may be an inappropriate datatype. It's not really a clock time. With `TIME`, you can have `42:00:00`. Since this time value seems to relate to a clock value and there is no 42-o'clock, you may be better off going with a `DATETIME` datatype. But that's a topic way outside this discussion. – Shawn Feb 06 '18 at 17:14
  • 1
    I believe ColdFusion will interpret your MySQL `TIME` field as a `DATETIME` with a default date part (server epoch). `TimeFormat()` should strip off the date part, but you are getting retranslated with the epoch date again somewhere. I don't know if Taffy is reinterpreting this before giving it back to you. You might need to show more code involved in this process. – Shawn Feb 06 '18 at 17:17
  • Try just dumping `#getLocationHours.clientHoursOfOperationOpenTime#+0. This should give you a decimal number. Is there anything to the left of the decimal? – Shawn Feb 06 '18 at 17:22
  • 1
    Depending on the version of ColdFusion that you are using, timeFormat(), datetimeFormat() can use "nn" instead of "mm" for the minutes. – Scott Jibben Feb 06 '18 at 20:54
  • @ScottJibben "mm" should work in the most recent versions, but it's been deprecated for "nn" in CF10. But this begins to break into the realm of the underlying Java (and it's SimpleDateFormat) and why ColdFusion wants to stay case-insensitive. Date masking is a _HUGE_ pain, especially when you mix languages. – Shawn Feb 07 '18 at 01:15
  • @Shawn, that seems to be the case for timeFormat() for now. Deprecated means stop using it, so I wouldn't recommend using 'm' in new code that uses timeFormat(). datetimeFormat() does require the use of 'n' for minutes. This doesn't appear to be the cause of the problem so I mentioned it in a comment instead of an answer. – Scott Jibben Feb 07 '18 at 16:17
  • 1
    @Shawn, I think that you are probably the closest regarding a solution. "January, 01 1970 17:00:00" is pretty close to unix epoch date for a value of zero. Older versions of MySQL would use zero to represent NULL. I'm still not sure why the "date" part would be appearing using a timeFormat() function though. – Scott Jibben Feb 07 '18 at 16:22
  • 1
    @ScottJibben " I'm still not sure why the "date" part would be appearing using a timeFormat() function though." -- One of the joys of the underlying Java of CF date formatting. For some real fun, try `timeFormat(datevar,"MM/DD/YYYY HH:NN:SS")`. :-/ – Shawn Feb 07 '18 at 18:41
  • @Shawn, very interesting use of timeFormat(). – Scott Jibben Feb 07 '18 at 19:13
  • 1
    @KishorePrabhakar I wonder if the query metadata is getting used to transpose the query data into a json struct and that either CF or Taffy is incorrectly determining the datatype from TIME to TIMESTAMP/DATETIME. I wonder if you transposed the returned query (hopefully just 1 row) into a struct and returned the struct if you'd get what you expect. I've had problems before with US zip codes getting transposed into JSON numbers instead of strings. So, you can get bit by the 'helpfulness' of CF. Shawn hinted at this as well. – Scott Jibben Feb 07 '18 at 19:20
  • @ScottJibben Rather, misuse of `timeformat()`. :-/ Gotta be careful with masking, especially when crossing between different languages. Depending on how much data this query is expected to return, it may be just as well off formatting it on the database and returning a simple string. Or making a MySQL view for it. – Shawn Feb 07 '18 at 19:42

0 Answers0