what is epoch time and how to convert into standard date format

-

 


what is epoch time?


Epoch time, also known as Unix time or POSIX time, is a system for representing time as the number of seconds that have elapsed since 00:00:00 UTC on January 1, 1970. It is widely used in computer systems as a standard way of representing time and is often used in databases, operating systems, and programming languages.


Epoch time is represented as a 10-digit integer, which represents the number of seconds that have elapsed since 00:00:00 UTC on January 1, 1970. This integer value is often referred to as a Unix timestamp or POSIX timestamp.


For example, the epoch time for 12:00:00 UTC on January 1, 2022, can be calculated as follows:

timestamp = (number of seconds between epoch time and 2022-01-01 12:00:00 UTC)

          = (1641062400 - 0)

          = 1641062400




How to convert epoch time to Regular time in Oracle SQL ?


SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(epoch_time/1000, 'SECOND') AS regular_time FROM dual;


we have to divide epoch_time by 1000 in order to convert milli seconds to seconds 


postgres: SELECT FROM_UNIXTIME(epoch_time) AS regular_time FROM some_table;

oracle SQL: SELECT TO_CHAR(TO_TIMESTAMP('epoch_time', 'SSSSS'), 'YYYY-MM-DD HH24:MI:SS') AS regular_time FROM dual;

Comments

Post a Comment

Popular posts from this blog

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to replace ASM failed disk?

-
Oracle Automatic Storage Management (ASM) was introduced in Oracle 10g. ASM provides advance storage management features such as DISK I/O re-balancing, volume management and easy database file name management. It also can provide MIRRORING of data for high availability and redundancy in the event of a disk failure (Mirroring is optional). ASM guarantees that data extents (table,index row data etc.) in one disk are mirrored in another disk (normal redundancy) and in two disks (high redundancy). A few times I have faced ASM disk failures when redundancy (mirroring) was enabled and none of them resulted in an issue for an end user. ASM automatically detects the disk failure and services Oracle SQL requests by retrieving information from the mirrored (other) disk. Such a failure is handled gracefully and entirely managed by Oracle. I am very impressed by the fault tolerance capability in ASM. But soon the Oracle DBA must work with the system administrator to replaced the failed disk. If t...
Read more

Shell scripting cheatsheet

-
 SED :  it is used to replace a word  Replace a word in a file:sed 's/old_word/new_word/' file.txt Replace all occurrences of a word in a file:sed 's/old_word/new_word/g' file.txt Delete lines matching a pattern:sed '/pattern/d' file.txt AWK: Print specific columns from a CSV file:awk -F ',' '{print $1, $3}' file.csv Calculate the sum of a column in a numeric file: awk '{sum += $1} END {print sum}' file.txt Filter lines based on a condition:awk '$3 > 50 {print $1, $3}' file.txt Shebang: The shebang, also known as a hashbang, is the first line of a script and specifies the interpreter to use. For example, #!/bin/bash Variables: Variables store data for later use. $name. To declare a variable as global, use the export keyword. Command substitution: For example, files=$(ls) stores the output of the ls command in the files variable. Control structures: if-else: Conditionally execute code based on a condition. It has the form: if co...
Read more