Converting Epoch Timestamp to SQL Server(Human Readable Format)

converting Epoch timestamp to sql server(human readable format)

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.

How to Convert EPOCH UNIX datetime to SQL format with SQL Server

An epoch timestamp represents the number of seconds elapsed since January 1st, 1970. In SQL Server, you can use dateadd() to perform this computation:

dateadd(second, start_timeslot_unix, '19700101') start_timeslot_datetime

If your epochs are expressed in milliseconds instead of seconds, then: dateadd(second, start_timeslot_unix / 1000, '19700101').

Converting epoch timestamp into a readable date

That epoch is in seconds, you need to multiply it by 1000.

Conversion of date from human-readable format to epoch fails

The C timing/calendrical API is very difficult to use correctly (which is why C++ is moving away from it).

From the C standard:

The value of tm_isdst is positive if Daylight Saving Time is in effect, zero if Daylight Saving Time is not in effect, and negative if the information is not available.

Set tmNow.tm_isdst = -1; prior to the call to mktime.

Unix epoch time converts to human readable incorrectly with PHP

The mistake is using m in the second date call. m is month, minutes is i.

echo date('Y.m.d', 1630440104).' at '.date('H:i:s', 1630440104);

or

echo date('Y.m.d \a\t H:i:s', 1630440104);

Just a side note, date second parameter accepts an int not a string in declare(strict_types=1); a TypeError would occur.

You can find the available formats under DateTime::format docs page.

Update epoch timestamp to dd.mm.yyyy hh.mm.ss.ms

One simple method is to add a computed column:

alter table dataset4_do_edycji add a_timestamp_dt as
    (dateadd(ms, a_timestamp%(3600*24*1000), 
             dateadd(day, a_timestamp/(3600*24*1000), '1970-01-01 00:00:00.0')
            )
    )

The column value is then calculated when you query it, so it is always up-to-date.

---

Related Topics