oracle convert unix epoch time to date
To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):
select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;
11/30/2011 5:00:00 AM
To convert that date back to milliseconds:
select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 * 1000
from dual;
1322629200000
If its seconds instead of milliseconds, just omit the 1000 part of the equation:
select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1322629200
from dual;
select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60
from dual;
Hope that helps.
Timestamp conversion to EPOCH time in Oracle SQL
1647932399 is the epoch for 2022-03-22 06:59:59 UTC. Your start time is 22-03-22 06:59:59 Asia/Jakarta, which is 22-03-21 23:59:59 UTC, and the epoch for that is 1647907199 - which is what you're getting. So your result is correct; but risky.
You are relying on implicit conversion.to_timestamp('22/03/2022 06:59:59','dd/mm/yyyy HH:MI:SS') gives you a plain timestamp, with no time zone information. When you then do SYS_EXTRACT_UTC(...) the plain timestamp is implicitly converted to a timestamp with time zone using your session time zone - which happens to be Jakarta, it seems. So it works - for you, in this session. If you change your session time zone, or more likely if someone else runs the same code from a session with a different time zone, then the reslut will be different.
To be more explicit, you can convert your string to a plain timestamp, then declare that it represents a Jakarta time, and then convert that to UTC:
sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))
21-MAR-22 23.59.59.000000000 UTC
There are then basically two ways to convert that to an epoch number; either cast it to a date, subtract 1970-01-01 as a date, and manipulate the resulting number of days (as in your question):
select
round(
(
cast(
sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))
as date)
- date '1970-01-01'
) * 24 * 60 * 60
) as result
from dual;
1647907199
Or leave it as a timestamp, subtract 1970-01-01 as a timestamp, and manipulate the resulting interval:
select
(extract(day from diff) * 24 * 60 * 60)
+ (extract(hour from diff) * 60 * 60)
+ (extract(minute from diff) * 60)
+ extract(second from diff)
as result
from (
select sys_extract_utc(from_tz(to_timestamp('22/03/2022 06:59:59', 'DD/MM/YYYY HH24:MI:SS'), 'Asia/Jakarta'))
- timestamp '1970-01-01 00:00:00' as diff
from dual
);
1647907199
Note that either way you can easily get the short result that was the basis for your previous question, by truncating the number or extracting just the days, without explicitly subtracting the 23:59:59 part. Either gives you 19072.
db<>fiddle
Oracle epoch date function for the previous month
You can convert your date range to an epoch using ( date_to_convert - DATE '1970-01-01' ) * 24 * 60 * 60 (assuming your epoch is in seconds since 1970).
You want to find values that are greater-than-or-equal-to the start of the previous month and before the start of the current month:
SELECT *
FROM your_table
WHERE epoch_column >= ( ADD_MONTHS( TRUNC(SYSDATE,'MM'), - 1 ) - DATE '1970-01-01' ) * 24 * 60 * 60
AND epoch_column < ( TRUNC( SYSDATE, 'MM' ) - DATE '1970-01-01' ) * 24 * 60 * 60
If you use midnight of the last day of the previous month as your upper bound then you will miss all values that are on that last day but are after midnight.
Convert Oracle DATE to Unix-style time (seconds since 1906?)
If that's number of seconds since Jan 01 1906, then:
SQL> select sysdate - date '1906-01-01' days,
2 (sysdate - date '1906-01-01') * 24 * 60 * 60 unix_style
3 from dual;
DAYS UNIX_STYLE
---------- ----------
41555,811 3590422068
SQL>
Why? Because - when you subtract two dates in Oracle, result is number of days. Then you have to multiply it by 24 (as there are 24 hours in a day), by 60 (as there are 60 minutes in an hour) and once again by 60 (as there are 60 seconds in a minute).
Of course, you could have multiplied it by 86400 (which is 24 * 60 * 60), but - former is difficult to understand while latter shows what's going on and why.
If - as Wernfried commented - date differs from the one you said, you'd just replace date '1906-01-01' with date '1970-01-01'.
Oracle: Error in converting DateTime to Epoch
You should use:
TO_TIMESTAMP_TZinstead ofTO_TIMESTAMP- the format model
YYYY-MM-DD"T"HH24:MI:SS.FF TZDrather than incorrectly usingMMtwice,HH24instead ofHH,.FFinstead ofXXX, andTZDinstead of hardcoding"Z". - Make sure you always convert your timestamp to UTC time zone (yours is already but others may not be)
- Don't
TRUNCate the timestamp to aDATEat midnight or you will lose the time component.
Like this:
SELECT ROUND(
(
TRUNC(timestamp_value AT TIME ZONE 'UTC', 'MI')
- DATE '1970-01-01'
) * 86400
+ EXTRACT(SECOND FROM timestamp_value AT TIME ZONE 'UTC')
) AS epoch_time
FROM (
SELECT TO_TIMESTAMP_TZ(
'2022-05-08T19:09:17Z',
'YYYY-MM-DD"T"HH24:MI:SS.FF TZD'
) AS timestamp_value
FROM DUAL
);
Which outputs:
EPOCH_TIME 1652033357
Related Topics
How to Select Only the First Rows for Each Unique Value of a Column
Doesn't Linq to SQL Miss the Point? Aren't Orm-Mappers (Subsonic, etc.) Sub-Optimal Solutions
SQL Update Fields of One Table from Fields of Another One
Oracle 'Partition By' and 'Row_Number' Keyword
Postgresql: How to Convert from Unix Epoch to Date
How to Find Out What Is Locking My Tables
Postgresql Tables Exists, But Getting "Relation Does Not Exist" When Querying
How to Implement a Keyword Search in MySQL
Count Cumulative Total in Postgresql
Grant Privileges for a Particular Database in Postgresql
Copy Data from One Column to Other Column (Which Is in a Different Table)
How to Select Records Without Duplicate on Just One Field in SQL
Postgresql Join with Array Type with Array Elements Order, How to Implement
How to Delete the Top 1000 Rows from a Table Using SQL Server 2008
Copy Table Structure to New Table in SQLite3