Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

converting Epoch timestamp to sql server(human readable format)

I have a problem in converting the Unix timestamp to sql server timestamp.

I have a data in excel sheet and I will import that data through a tool. So I am looking for a code or syntax which can convert that Epoch timestamp to sql server timestamp.

I have 3 different columns with the same format. How can I change the values in those columns.

For Example:

  • Epoch timestamp ---1291388960
  • sql server timestamp--- 2010-12-03 15:09:20.000
like image 422
Shahsra Avatar asked Jan 24 '11 22:01

Shahsra


People also ask

How do you convert epoch time to human readable?

Convert from epoch to human-readable dateString date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000)); Epoch in seconds, remove '*1000' for milliseconds. myString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer. Replace 1526357743 with epoch.

How do I change epoch time in SQL?

Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.

How do I get epoch time in SQL?

To get epoch time, we will define our start_endtime as: '1970-01-01 00:00:00' and our part parameter as s (to get epoch time in seconds). After this, we convert our result to BIGINT datatype using CAST().

How do I convert epoch time to manual date?

use strict; use warnings; use Time::Piece; my $datestring = '07-06-2019 21:13:00'; my $time = localtime->strptime($datestring, '%d-%m-%Y %H:%M:%S'); my $epoch = $time->epoch; ... my $time = localtime($epoch); my $datestring = $time->strftime('%d-%m-%Y %H:%M:%S');


2 Answers

I have 3 different columns with the same format. How can I change the values in those columns.

To update 3 columns in a table, you can pair DATEADD seconds to the epoch (1 Jan 1970) with the column name, i.e.

update tbl set     datetimecol1 = dateadd(s, epochcol1, '19700101'),     datetimecol2 = dateadd(s, epochcol2, '19700101'),     datetimecol3 = dateadd(s, epochcol3, '19700101') 

You can't update in place since a bigint column cannot also be a datetime column. You have to update them into 3 other columns.

like image 78
RichardTheKiwi Avatar answered Sep 28 '22 13:09

RichardTheKiwi


Use the DATEADD function:

SELECT DATEADD(ss, 1291388960, '19700101') 

...specifying a date of January 1st, 1970. In this example, it was provided in the YYYYMMDD format.

DATEADD will return a DATETIME data type, so if you have a table & column established -- you can use the function to INSERT/UPDATE depending on your needs. Provide details, and I'll clarify. Once you have a DATETIME to work with, you can use CAST or CONVERT to format the date in TSQL.

like image 24
OMG Ponies Avatar answered Sep 28 '22 14:09

OMG Ponies