910968_en-US

liliconcom

How to convert UTC timestamp to local time in a column formula

Hi all,

I have a column Timestamp UTC in JMP with values like 2025-10-31T08:30:00.
I want to create a new column Timestamp Local that converts this UTC time to local time

Is there a simple formula-based way to add 5.5 hours to my timestamp directly in a column formula, without scripting or using Date Add?

Something like:

Informat(:Timestamp UTC, "yyyy-mm-ddThh:mm:ss") + (4 * 3600)

 but I’m not sure if this is the right syntax as it doesn't yield result nor formula evaluation errors

I also tried based on a discussion thread: 

col_name_list = Current Data Table() << get column names( string );

If(
	!Contains(col_name_list, "Timestamp Local"),
	Current Data Table() << New Column(
		"Timestamp Local",
		Numeric,
		Continuous,
		Format("m/d/y h:m:s"),
		Formula(
			Informat(:Name("Timestamp UTC"),
			//"Format Pattern",
			"yyyy-mm-ddThh:mm:ss") + (4* 3600)
		)
	)
);
Informat(:Timestamp UTC, "Format Pattern", "<YYYY><-><MM><-><DL><DD><'T'><HH24><::><MM><::><SS>Z") + (4 * 3600)

<P>- No result</P>
<P>&nbsp;</P>
<P>The Formula looks like:&nbsp;</P>
<P>Also tried Format -&nbsp;<CODE class="language-jsl">"yyyy-mm-ddThh:mm:ss"

Would appreciate any guidance or examples on how to handle UTC to local conversions cleanly within column formulas.

Thanks!

Automation and ScriptingBasic Data Analysis and ModelingData Blending and CleanupRe: How to convert UTC timestamp to local time in a column formula

Is this an option?

</CODE></PRE>
<P> </P>
<PRE><CODE class=" language-jsl">New Column( "Time Plus",
Numeric,
"Continuous",
Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),
Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
Formula( :Timestamp + In Hours( 5.5 ) ),
Set Selected
)

Re: How to convert UTC timestamp to local time in a column formula

New Table( "Untitled",
	Add Rows( 2 ),
	New Column( "old", Character, "Nominal", Set Values( {"2025-10-31T08:30:00", "2025-10-31T08:30:01"} ), Set Display Width( 153 ) ),
	New Column( "new",
		Numeric,
		"Continuous",
		Format( "Monddyyyy h:m:s", 22, 0 ),
		Input Format( "Monddyyyy h:m:s", 0 ),
		Formula( Informat( :old, "yyyy-mm-ddThh:mm:ss" ) + In Hours( 5.5 ) )
	)
)

JMP stores date-time values as numeric seconds since 1904. The InHours function returns 3600 * the argument. I'm assuming you've got character data in the Timestamp column (old, in my example.)

Re: How to convert UTC timestamp to local time in a column formula

I should have added this for reference.

Since, this is from raw data - modeling ttype - continuous

 

Format defined  as: 

		Format("m/d/y h:m:s")

Re: How to convert UTC timestamp to local time in a column formula

I should have added this for reference.

Since, this is from raw data - modeling ttype - continuous

 

Format defined  as: 

		Format("m/d/y h:m:s")<BR /><BR />I&nbsp;tried&nbsp;the&nbsp;formula&nbsp;above,&nbsp;it&nbsp;didn't&nbsp;yeild&nbsp;any&nbsp;output.&nbsp;I&nbsp;will&nbsp;check&nbsp;again.&nbsp;

 

Re: How to convert UTC timestamp to local time in a column formula

@mlo1 @Craige_Hales 

This has worked. 

col_name_list = Current Data Table() << get column names( string );
If( !Contains( col_name_list, "Timestamp Local" ),      //If the column name doesn't exist in the array col_name_list
	Current Data Table() << New Column(
	"Timestamp Local",
	Numeric,
	"Continuous",
	Format( "m/d/y h:m:s", 22, 0),
	//Input Format( "Monddyyyy h:m:s", 0  ),
	Formula( 
	//In Format( :Timestamp UTC, "yyyy-mm-ddThh:mm:ss" ) + In Hours( 5.5 ) 
	Informat( Substitute(:Timestamp UTC, "+00:00", ""), "yyyy-mm-dd hh:mm:ss" ) + In Hours(5.5)	
	),
	//Formula( :Timestamp + In Hours( 5.5 ) ),
	//Set Selected
		)
);<BR /><BR />

TIme UTC	Time Local
2025-09-14 07:51:52+00:00	09/14/2025 1:21:52 PM
2025-09-14 07:56:52+00:00	09/14/2025 1:26:52 PM

 

 

Re: How to convert UTC timestamp to local time in a column formula

However, this formula doesn't work

If(!Contains(col_name_list, "Max Timestamp UTC"),
	Current Data Table() << New Column(
		"Max Timestamp UTC",
		Numeric,
		Continuous,
		Format("m/d/y h:m:s", 22, 0),
		Formula(
			Col Maximum(
				:Name("Timestamp UTC"),
				/*Informat(
					Substitute(:Name("Timestamp UTC"), "+00:00", ""),
					"yyyy-mm-dd hh:mm:ss"
				),*/
				:Name("ID")
			)
		)
	)
);

The as is version, commented part of the code seems to take longer goes into an infinite loop as it causes my JMP to not respond and I have to kill the application. 

I have done this earlier but local time stamp is generated  without using below code. 

:Name("Timestamp UTC") << Data Type(Character) << Modeling Type(Nominal);

Re: How to convert UTC timestamp to local time in a column formula

Which JMP version are you using? If you are having some issues with the speed with Col Max I would first create separate column with

Informat(Substitute(:"Timestamp UTC"n, "+00:00", ""), "yyyy-mm-dd hh:mm:ss")

and then use that newly created column as by variable. 

Re: How to convert UTC timestamp to local time in a column formula

Yes, I had tried that

 

dt:Name( "Timestamp UTC" ) << Informat(
	Substitute( :Timestamp UTC, "+00:00", "" ),
	"yyyy-mm-dd hh:mm:ss"
);

dt:Name( "Timestamp UTC" ) << Data Type( Character ) << Modeling Type( Nominal );

col_name_list = Current Data Table() << get column names ( string );
If( !Contains(col_name_list, "Timestamp UTC (Numeric)"),
	Current Data Table() << New Column(
    "Timestamp UTC (Numeric)",
    Numeric,
    "Continuous",
    Format("m/d/y h:m:s", 22, 0),
    Formula(
        Informat( Substitute(:Timestamp UTC, "+00:00", ""), "yyyy-mm-dd hh:mm:ss" )
    )
)
);

dt:Name("Timestamp UTC (Numeric)") << Delete Formula;
dt:Name("Timestamp UTC") <<
	Set Values(:Name("Timestamp UTC (Numeric)")) <<
	Data Type(Numeric) <<
	Format("m/d/y h:m:s", 22, 0);

 

 

I am trying this. It's overwriting my timestamp utc column and no values

 

Re: How to convert UTC timestamp to local time in a column formula

Can you provide example of the table you are using? Just the timestamp columns should be enough (also remember to drop table variables/scripts).

Re: How to convert UTC timestamp to local time in a column formula

@jthi Refer this solution. I found it hassle free to create an extra column and use that.