4

I have a six digit time string coming from .NET I would like to convert to a timestamp in ColdFusion. What would be the correct way to convert this?

string= 20190126195631

The date is simple:

thisDate = '#mid(string,5,2)#/#mid(string,7,2)#/#mid(string,1,4)#'

To get the time I've tried: 

timestamp = timeFormat(parseDateTime(mid(string,9,6)),'HH:mm')

What do I do with the 195631 to show the timestamp?

Stephen Sharpe
  • 167
  • 1
  • 8

3 Answers3

4

(Too long for comments...)

time = '#mid(string,9,2)-8#

Don't just subtract 8 from the hour because it'll return an invalid values like "-1" AM or "-8" AM, when the UTC value is between midnight and 7AM.

Also, if you're converting the value to local time, don't forget about daylight savings time changes. An offset of 8 hours may change to 7 hours, depending on the time of year and zone. Instead of using a hard coded number, take a look at the DateConvert function to convert UTC to local time and using ParseDateTime with a mask, instead of multiple string functions.

Keep in mind there are pros, cons .. and sadly potential bugs/gotchas .. with all of the suggested approaches, depending on your version of CF. So be sure to test with variety of date values and time zones.

ColdFusion 2016+ syntax (Note, uses "nn" for minutes. Runnable Example)

str = "20190126175631";
utcDate = parseDateTime(str, "yyyyMMddHHnnss");
localDate = DateConvert("utc2Local", utcDate);

writeOutput("utcDate ="& utcDate &" localDate = "& localDate);

ColdFusion 10/11 syntax (Note, uses "mm" for minutes. Runnable Example)

str = "20190126175631";
utcDate = parseDateTime(str, "yyyyMMddHHmmss");
localDate = DateConvert("utc2Local", utcDate);

writeOutput("utcDate ="& utcDate &" localDate = "& localDate);
SOS
  • 6,430
  • 2
  • 11
  • 29
  • 1
    Yes I had a -8 error at about 4 P.M. PST because 0-8 was not a valid time. The dateConvert is the correct solution. Thanks! – Stephen Sharpe Jan 27 '19 at 22:08
  • Looks like pop conversion uses mm for minutes rather than nn Message: yyyyMMddHHnnss is not a valid date format. yyyyMMddHHmmss https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html – Stephen Sharpe Jan 28 '19 at 16:36
  • **Updated with example** @StephenSharpe - Yeah, `nn` works in CF2016+, while CF10/11 uses `mm` (which is what I was implying by gotchas/depending on version). Are you using CF10/11? If so, might want to update the question tags. I (wrongly) assumed you were using 2016+ – SOS Jan 28 '19 at 17:01
  • @CharlesRobertson - Simpler, but ...since Adobe broke backward compatability :-/ ... thinking about it, String functions (or Java) is more resilient for the parsing aspect when you upgrade. I'd keep DateConvert for the translation to local time though. – SOS Jan 29 '19 at 23:29
2

Thanks! Your comments helped me realize the time is simply: HHMMSS 19 = 7 p.m., 56 mins and 31 secs.

What threw me off was the GMT (-8 hours for me) shift. So the answer is: 

time = '#mid(string,9,2)-8#:#mid(string,11,2)#'
SOS
  • 6,430
  • 2
  • 11
  • 29
Stephen Sharpe
  • 167
  • 1
  • 8
  • 1
    The above will fail under some conditions. See [more details](https://stackoverflow.com/a/54383387/8895292) – SOS Jan 27 '19 at 03:27
  • Edit - wrong link. Should be https://stackoverflow.com/a/54384804/8895292 – SOS Jan 27 '19 at 20:17
2

I might be going mad here, but it looks like the string could be split like this:

2019-01-26 19:56:31

So, using Coldfusion:


<cfset datetimestring = "20190126195631">

<cfset year = Mid(datetimestring,1,4)>
<cfset month = Val(Mid(datetimestring,5,2))>
<cfset day = Val(Mid(datetimestring,7,2))>
<cfset hour = Val(Mid(datetimestring,9,2))>
<cfset minute = Val(Mid(datetimestring,11,2))>
<cfset second = Val(Mid(datetimestring,13,2))>


<cfset dateTime = CreateDateTime(year,month,day,hour,minute,second)>

<cfset dateTimeOffset = DateAdd("h", -8, dateTime)>

<cfoutput>
#dateTimeOffset#
</cfoutput>

As, I said this string could just be an epoch timestamp, but it does look like a 'date time' pattern?

Charles Robertson
  • 1,760
  • 16
  • 21