CODEWITHSUNDEEP
xsltxslt-3.0xslt-grouping
Velsus
Velsus

Reputation: 25

XSLT convert date and time from PST to UTC

In my XSLT, I'm outputting a time stamp:

<time_stamp>
    <xsl:value-of select="format-dateTime(wd:Last_Functionally_Updated, '[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')"/>
</time_stamp>

However, the source field <wd:Last_Functionally_Updated> is in PST, how do I convert it to UTC? UTC is 8 hours ahead of PST, for example, 1PM PST is 9PM UTC

Below is the XML

<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.workday.report/INT0194_Recruitics">
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>0</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2023-06-26T12:50:11.949-07:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2023-06-26T12:50:11.949-07:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Rejected">
                <wd:ID wd:type="WID">9a35fa5882c24620a777de1dbcf34d3c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REJECTED</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0000704156</wd:candidate>
        <wd:Requisition_ID>R-0000051703</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>0</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-02-29T13:26:32.874-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-02-29T13:26:32.874-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Rejected">
                <wd:ID wd:type="WID">9a35fa5882c24620a777de1dbcf34d3c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REJECTED</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0000704156</wd:candidate>
        <wd:Requisition_ID>R-0000076253</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>0</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:41:35.698-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:41:54.044-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:42:50.630-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Screen">
                <wd:ID wd:type="WID">2d0e4a68825d4d2a8c507af7542d3f15</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">SCREEN</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:43:19.091-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Screen">
                <wd:ID wd:type="WID">2d0e4a68825d4d2a8c507af7542d3f15</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">SCREEN</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:43:39.613-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Assessment">
                <wd:ID wd:type="WID">bb01594c858710000e4d46d2b7220034</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">ASSESSMENT</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:45:00.608-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Interview">
                <wd:ID wd:type="WID">3317e4265cf94b91873b7015881a0e9c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">INTERVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:52:33.923-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Offer (Employment Agreement)">
                <wd:ID wd:type="WID">1d57fbe8504d100015945d2b2704006a</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">EMPLOYMENT_AGREEMENT</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:56:25.485-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Background Check">
                <wd:ID wd:type="WID">be56571f9f6f45ca82871c8bec853f5e</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">BACKGROUND</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-11T09:56:25.485-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Ready for Hire">
                <wd:ID wd:type="WID">edd55803e2ec49c3a6eb01daadfc9bf9</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">HIRED</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721053</wd:candidate>
        <wd:Requisition_ID>R-0000106433</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>1</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-10T12:15:30.187-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-10T12:15:49.308-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Interview">
                <wd:ID wd:type="WID">3317e4265cf94b91873b7015881a0e9c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">INTERVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-10T12:15:49.308-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Offer">
                <wd:ID wd:type="WID">868a8156682541f2aadc2fe90268b75f</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">OFFER</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721051</wd:candidate>
        <wd:Requisition_ID>R-0000098017</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>1</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-12T11:52:49.381-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-12T11:52:49.381-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Interview">
                <wd:ID wd:type="WID">3317e4265cf94b91873b7015881a0e9c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">INTERVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721060</wd:candidate>
        <wd:Requisition_ID>R-0000103689</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>1</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-12T11:52:49.381-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-12T11:52:49.381-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Interview">
                <wd:ID wd:type="WID">3317e4265cf94b91873b7015881a0e9c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">INTERVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721060</wd:candidate>
        <wd:Requisition_ID>R-0000103689</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>1</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-16T09:52:14.424-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-16T09:52:14.424-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Rejected">
                <wd:ID wd:type="WID">9a35fa5882c24620a777de1dbcf34d3c</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REJECTED</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721062</wd:candidate>
        <wd:Requisition_ID>R-0000086280</wd:Requisition_ID>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Job_Requisition_group>
            <wd:Is_Evergreen>1</wd:Is_Evergreen>
        </wd:Job_Requisition_group>
        <wd:Job_Application_Process_Statuses_group>
            <wd:Last_Functionally_Updated>2024-12-17T22:12:11.799-08:00</wd:Last_Functionally_Updated>
            <wd:Stage wd:Descriptor="Review">
                <wd:ID wd:type="WID">31350d72f899430fb446b410c1e368cd</wd:ID>
                <wd:ID wd:type="Recruiting_Stage_ID">REVIEW</wd:ID>
            </wd:Stage>
        </wd:Job_Application_Process_Statuses_group>
        <wd:candidate>C-0001721063</wd:candidate>
        <wd:Requisition_ID>R-0000085728</wd:Requisition_ID>
    </wd:Report_Entry>
</wd:Report_Data>

Below is the XSLT I'm using

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:wd="urn:com.workday.report/INT0194_Recruitics"
    exclude-result-prefixes="wd">
    
    <xsl:output method="text" omit-xml-declaration="no"/>
    <xsl:variable name="RECORD-DELIMITER">
        <xsl:text>&#xd;&#xa;</xsl:text>
    </xsl:variable>
    <xsl:param name="Dilim">","</xsl:param>
    <xsl:param name="Quote">"</xsl:param>
    <xsl:param name="Comma">,</xsl:param>
    <xsl:variable name="FIELD-DELIMITER">
        <xsl:value-of select="$Dilim"/>
    </xsl:variable>
    <xsl:variable name="STARTING-QUOTE">
        <xsl:value-of select="$Quote"/>
    </xsl:variable>
    
    <xsl:template match="wd:Report_Data">
        <Header>
            <HeaderItem1>"time_stamp"</HeaderItem1>
            <xsl:value-of select="$Comma"/>
            <HeaderItem2>"event"</HeaderItem2>
            <xsl:value-of select="$Comma"/>
            <HeaderItem3>"applyflow_id"</HeaderItem3>
            <xsl:value-of select="$Comma"/>
            <HeaderItem4>"job_id"</HeaderItem4>
        </Header>
        <xsl:value-of select="$RECORD-DELIMITER"/>

        <xsl:for-each-group select="wd:Report_Entry[wd:Job_Requisition_group/wd:Is_Evergreen = 0]/wd:Job_Application_Process_Statuses_group" composite="yes" group-by="../wd:candidate, ../wd:Requisition_ID, ../wd:Job_application_ID, wd:Stage/wd:ID">
            <xsl:for-each select="(current-group() => sort((), function($j) { $j/wd:Last_Functionally_Updated }))[last()]">
                <xsl:value-of select="$STARTING-QUOTE"/>
                    <time_stamp>
                        <xsl:value-of select="format-dateTime(wd:Last_Functionally_Updated, '[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')"/>
                    </time_stamp>
                <xsl:value-of select="$FIELD-DELIMITER"/>
                     <event>
                         <xsl:value-of select="wd:Stage/@wd:Descriptor"/>
                     </event>
                <xsl:value-of select="$FIELD-DELIMITER"/>
                    <applyflow_id>
                        <xsl:value-of select="../wd:candidate"/>
                    </applyflow_id>  
                <xsl:value-of select="$FIELD-DELIMITER"/>
                    <job_id>
                        <xsl:value-of select="../wd:Requisition_ID"/>
                    </job_id>
                <xsl:value-of select="$STARTING-QUOTE"/>
                <xsl:value-of select="$RECORD-DELIMITER"/>
            </xsl:for-each>
        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

I almost made it work using <xsl:variable name="PST_To_UTC" select="adjust-dateTime-to-timezone(wd:Last_Functionally_Updated, xs:dayTimeDuration('PT1H'))"/>, but it doesn't really work because that in XML, some time value has -07:00 (for example, 2023-06-26T12:50:11.949-07:00) and some has -08:00 (for example, 2024-02-29T13:26:32.874-08:00) so PT1H only works with when it's -07:00. Maybe I should try using a substring to remove the "-07:00" part but didn't figure out how. Thanks in advance.

Upvotes: 0

Views: 71

Answers (1)

Conal Tuohy
Conal Tuohy

Reputation: 3258

I may be confused as to the details of your question, but my understanding is:

  • the dateTime values in your example are all recorded in the local time of a particular place in the North American Pacific timezone (i.e. the timezone considered as a geographical area), and therefore have a timezone offset of either -7 hours or -8 hours from UTC.
  • you want to convert these dateTime values to UTC, but format them for display using the American convention of month/day/year.

For a start, it's important to be clear that the word "timezone" has a couple of quite different senses; a geographical sense (a geographical area in which certain time-keeping rules apply), and a horological sense (i.e. a specific clock setting which is an offset from UTC). So your "Pacific" geographical timezone actually corresponds to two different timezones (in the sense of offsets from UTC) depending on the time of year: PDT (UTC−07:00) in the warmer half of the year and PST (UTC−08:00) in the cooler half of the year. That would explain why the dateTime values in your example XML use one or the other offset, depending on the time of year.

What I think I'd do is adjust the dateTime values to the UTC timezone (i.e. with a zero offset), and then format the resulting UTC dateTime for display.

To show what I'm talking about, consider the two example dateTime values you mentioned:

<dates>
  <date>2023-06-26T12:50:11.949-07:00</date>
  <date>2024-02-29T13:26:32.874-08:00</date>
</dates>

The following XPath expression converts those string values to xs:dateTime values, and formats them, and then adjusts the xs:dateTime values to use the UTC offset (i.e. zero), and then formats those UTC dates:

let 
    $dates := //date ! xs:dateTime(.),
    $formatted-dates := $dates ! format-dateTime(., '[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]'),
    $utc-dates := $dates ! adjust-dateTime-to-timezone(., xs:dayTimeDuration('PT0H')),
    $formatted-utc-dates := $utc-dates ! format-dateTime(., '[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')
return
  (
    $dates, $formatted-dates, 
    $utc-dates, $formatted-utc-dates
  )

Results:

xs:dateTime("2023-06-26T12:50:11.949-07:00")
xs:dateTime("2024-02-29T13:26:32.874-08:00")
"06/26/2023 12:50:11"
"02/29/2024 13:26:32"
xs:dateTime("2023-06-26T19:50:11.949Z")
xs:dateTime("2024-02-29T21:26:32.874Z")
"06/26/2023 19:50:11"
"02/29/2024 21:26:32"

So I think it should be enough to replace your <time_stamp> expression with something along these lines:

<time_stamp><xsl:value-of select="
    wd:Last_Functionally_Updated
    => xs:dateTime()
    => adjust-dateTime-to-timezone(xs:dayTimeDuration('PT0H'))
    => format-dateTime('[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')
"/></time_stamp>

For efficiency, though, the expression xs:dayTimeDuration('PT0H') that represents the UTC timezone should be computed once and stored in a variable to be referenced by name.

However, I do have some other suggestions for your stylesheet which are just stylistic, but which I think you may find helpful:

Firstly, I'd recommend you check out "Text Value Templates" in the XSLT 3.0 spec. TVTs provide an alternative syntax to <xsl:value-of/> statements, which is generally more concise and readable, especially for stylesheets like yours which is generating plain text. TLDR: if you add an expand-text="yes" attribute to the stylesheet element then you can use { and } inside text nodes to evaluate XPath expressions, in the same way as you've always been able to do in attributes (with Attribute Value Templates).

Secondly, variable assignments like this:

    <xsl:variable name="STARTING-QUOTE">
        <xsl:value-of select="$Quote"/>
    </xsl:variable>

... can be written more succinctly like this:

    <xsl:variable name="STARTING-QUOTE" select="$Quote"/>

Thirdly, I note that you create a bunch of XML elements such as <Header> and <HeaderItem> and <time_stamp> which are only temporary in the sense that they're discarded from the result document because you use the text output method. You could omit them altogether from your stylesheet, though I guess you may have put them there deliberately as a kind of documentation (i.e. as a kind of comment), which would be a reasonable choice to have made. But I think if you adopt text value templates in place of <xsl:value-of> then the added readability you'd get from the text value templates will make these "documentary" elements unnecessary, and if you wanted to keep them as comments you could write them as XPath comments (i.e. enclosed in (: and :) e.g.

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:wd="urn:com.workday.report/INT0194_Recruitics"
    exclude-result-prefixes="wd"
    expand-text="yes">
    
    <xsl:output method="text" omit-xml-declaration="no"/>
    <xsl:variable name="RECORD-DELIMITER">
        <xsl:text>&#xd;&#xa;</xsl:text>
    </xsl:variable>
    <xsl:param name="Dilim">","</xsl:param>
    <xsl:param name="Quote">"</xsl:param>
    <xsl:param name="Comma">,</xsl:param>
    <xsl:variable name="FIELD-DELIMITER" select="$Dilim"/>
    <xsl:variable name="STARTING-QUOTE" select="$Quote"/>
    <xsl:variable name="utc-timezone" select="xs:dayTimeDuration('PT0H')"/>

    <xsl:template match="wd:Report_Data">
        <xsl:text>"time_stamp"{$Comma}"event"{$Comma}"applyflow_id"{$Comma}"job_id"{$RECORD-DELIMITER}</xsl:text>
        <xsl:for-each-group select="wd:Report_Entry[wd:Job_Requisition_group/wd:Is_Evergreen = 0]/wd:Job_Application_Process_Statuses_group" composite="yes" group-by="../wd:candidate, ../wd:Requisition_ID, ../wd:Job_application_ID, wd:Stage/wd:ID">
            <xsl:for-each select="(current-group() => sort((), function($j) { $j/wd:Last_Functionally_Updated }))[last()]">{
              concat(
                $STARTING-QUOTE,
                (: 'time_stamp' is the date re-expressed in UTC timezone :)
                wd:Last_Functionally_Updated
                  => xs:dateTime()
                  => adjust-dateTime-to-timezone($utc-timezone)
                  => format-dateTime('[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]'),
                $FIELD-DELIMITER,
                (: event :)
                wd:Stage/@wd:Descriptor,
                $FIELD-DELIMITER,
                (: applyflow_id :)
                ../wd:candidate,
                $FIELD-DELIMITER,
                (: job_id :)
                ../wd:Requisition_ID,
                $STARTING-QUOTE,
                $RECORD-DELIMITER
              )
            }</xsl:for-each>
        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

Fourthly, I would probably write a csv:row function to convert a sequence of field values into a single string in which each value is surrounded by our $Quote, and the quoted values are separated with our $Comma separator. This same function would be able to produce both the header row and the data rows:

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:wd="urn:com.workday.report/INT0194_Recruitics"
    xmlns:csv="https://en.wikipedia.org/wiki/Comma-separated_values"
    exclude-result-prefixes="wd"
    expand-text="yes">
    
    <xsl:output method="text" omit-xml-declaration="no"/>
    <xsl:variable name="RECORD-DELIMITER" select=" '&#xd;&#xa;' "/>
    <xsl:param name="Quote">"</xsl:param>
    <xsl:param name="Comma">,</xsl:param>
    <xsl:variable name="utc-timezone" select="xs:dayTimeDuration('PT0H')"/>
    
    <!-- format a row of field values -->
    <xsl:function name="csv:row">
      <xsl:param name="fields"/>
      <xsl:value-of select="
        string-join(
          for $field in $fields return $Quote || $field || $Quote,
          $Comma
        ) || $RECORD-DELIMITER
      "/>
    </xsl:function>

    <xsl:template match="wd:Report_Data">
        <xsl:text>{
          csv:row(
            ("time_stamp", "event", "applyflow_id", "job_id")
          )
        }</xsl:text>
        <xsl:for-each-group select="wd:Report_Entry[wd:Job_Requisition_group/wd:Is_Evergreen = 0]/wd:Job_Application_Process_Statuses_group" composite="yes" group-by="../wd:candidate, ../wd:Requisition_ID, ../wd:Job_application_ID, wd:Stage/wd:ID">
            <xsl:for-each select="(current-group() => sort((), function($j) { $j/wd:Last_Functionally_Updated }))[last()]">{
              csv:row(
                (
                  (: time_stamp (in UTC) :)
                  wd:Last_Functionally_Updated
                    => xs:dateTime()
                    => adjust-dateTime-to-timezone($utc-timezone)
                    => format-dateTime('[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]'),
                  (: event :)
                  wd:Stage/@wd:Descriptor,
                  (: applyflow_id :)
                  ../wd:candidate,
                  (: job_id :)
                  ../wd:Requisition_ID
                )
              )
            }</xsl:for-each>
        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

The question of how much of your stylesheet you write in the form of XPath expressions, and how much you use XSLT elements such as <xsl:value-of> and <xsl:for-each> is a matter of personal preference, and I know that my general preference is unusually skewed towards the XPath end of that spectrum. So take it with a grain of salt: it's up to you to decide the style you prefer, and I just offer it as a possible alternative style to consider.

In my personal view, an XSLT-light/XPath-heavy style of XSLT is often a good one to use for tasks such as this which generate plain text rather than elements and where the logical structure of the data is strictly tabular (i.e. spreadsheets, database tables, tabular reports, etc). Whereas if your source document has an irregular structure, and you have a number of templates which each match alternative structural elements, then this style may be less appropriate.

Upvotes: 0

Related Questions