SQL文でTIMESTAMP型を使って現在日付から何日前かを出力したい

Question

前提・実現したいこと

SQLPlusでTIMESTAMP型で設定した日時が現在日時から何日前のものかを出すSQL文を作っています

発生している問題・エラーメッセージ

(SYSTIMESTAMP-LOGIN_DATE_AND_TIME) AS 最終ログイン日時(日前)
の結果以下のようになりました

+0000000「00」 00:23:08.733000
+000000「815」 16:25:00.733000
+000000「151」 15:24:45.733000
+0000000「87」 09:17:55.733000
+0000000「28」 02:47:43.733000

「」内が何日前のところになると思うのですがそこだけを抽出することは可能でしょうか

該当のソースコード

SELECT PLAYER_LIST.PLAYER_ID AS プレイヤーID,
PLAYER_NAME AS 名前,
(SYSTIMESTAMP-LOGIN_DATE_AND_TIME) AS 最終ログイン日時(日前)
FROM DUAL,PLAYER_LIST;

※LOGIN_DATE_AND_TIMEには設定した日時が入っています

試したこと

日にちだけ引けないものかと試してみましたが

SELECT PLAYER_LIST.PLAYER_ID AS プレイヤーID, PLAYER_NAME AS 名前,(SYSTIMESTAMP(dd)-LOGIN_DATE_AND_TIME(dd)) AS 最終ログイン日時(日前)
*
行1でエラーが発生しました。:
ORA-30088: datetime/intervalの精度が範囲外です

と出てしまいました。

Comments

[sazi]

> +0000000「00」 00:23:08.733000
上記のようなデータは何を表してますか？
LOGIN_DATE_AND_TIMEの内容？

[退会済みユーザー]

(SYSTIMESTAMP-LOGIN_DATE_AND_TIME) AS 最終ログイン日時(日前)の結果になります！

[sazi]

質問にあるSQLの結果という事ですね。

[Orlofsky]

SQLPlusだから[Oracle]タグを追加してください。
同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで明示したり、バージョンも明記した方が適切なコメントが付き易いです。
https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html

[Orlofsky]

質問に LOGIN_DATE_AND_TIME 列のデータ型やその列が存在するテーブル名がわかるように CREATE TABLE, LOGIN_DATE_AND_TIME の内容がわかるようにデータのINSERTを提示できると適切なコメントが付き易いです。

[退会済みユーザー]

次回からそうしますアドバイスありがとうございます。

[Orlofsky]

モラルがあるなら、今回から直せた方が良いですよ。
[Oracle]タグを追加すると https://teratail.com/tags/Oracle に載るので同じ疑問を持った人がこの質問を見つけやすくなります。

Answer 1

SQLPlus とあるので　Oracle でのことと仮定します。

Oracle では TIMESTAMP - TIMESTAMP は INTERVAL になりますので、整数部が日、小数部で時間以下を表現しています（1=24時間=1440分=86400秒）。

ので、結果から類推したとおりで概ね正しいのですが、より確実にするには

SELECT
  PLAYER_LIST.PLAYER_ID,
  EXTRACT(day FROM (SYSTIMESTAMP-LOGIN_DATE_AND_TIME))
...

と、EXTRACT 関数を使ってください。

※ただし、例えば現在時刻=12:00 で最終ログインが前日の19:00 など、期間としては1日未満の場合、0 となるので注意してください

Comments

[退会済みユーザー]

しらなかった関数なので勉強になりました。ありがとうございます！

Answer 2

経過日数を求めたいという事ですね。

SELECT PLAYER_LIST.PLAYER_ID AS プレイヤーID, PLAYER_NAME AS 名前
      , SYSDATE - TO_DATE(TO_CHAR(LOGIN_DATE_AND_TIME, 'YYYY/MM/DD')) AS 前回ログイン間隔
FROM PLAYER_LIST

Comments

[退会済みユーザー]

ありがとうございます！