unix timestamp to datetime

Question

unix timestamp to datetime

SSCrazy

Points: 2107

March 22, 2024 at 12:06 am

I have a sql table where the app is writing. A unix timestamp value to the field. I would like to iterate thru the table and pull from a specific datetime but not sure how to code that in PS that also would export to csv file.

I just pulled the latest record from table --

t_stamp=1711065382576

My Current datetime 3/21/2024 07:56PM

Data Record

7384, , 57.3080520629883 ,, , 192, 1711065382576

CREATE TABLE [dbo].[plc_data_4_2024_03](
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
[tagid] ASC,
[t_stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16215 More actions · Answer 1

you can start by reading the manual - https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-7.4

and as alternative, you pass your desired date in date format to your sql, and within the sql itself you convert to your unixtimestamp - which you have already been told many times how to do.

Jeff Moden SSC Guru Points: 1004591 More actions Follow · Answer 2

Why on Earth would you use PoSh to do this? You have the data in a table already.

https://www.sqlservercentral.com/articles/convert-unix-timestamps-to-date-times-in-sql-server-part1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

unix timestamp to datetime

Cookies on SQLServerCentral