Ufdjrw

How much RAM could one put in a typical 80386 setup?

Why are electrically insulating heatsinks so rare? Is it just cost?

Can divisibility rules for digits be generalized to sum of digits

Do I have a twin with permutated remainders?

Is it possible to do 50 km distance without any previous training?

Have astronauts in space suits ever taken selfies? If so, how?

Is it tax fraud for an individual to declare non-taxable revenue as taxable income? (US tax laws)

Is it important to consider tone, melody, and musical form while writing a song?

Writing rule stating superpower from different root cause is bad writing

What does it mean to describe someone as a butt steak?

Is it legal for company to use my work email to pretend I still work there?

Show that if two triangles built on parallel lines, with equal bases have the same perimeter only if they are congruent.

What's the point of deactivating Num Lock on login screens?

Mage Armor with Defense fighting style (for Adventurers League bladeslinger)

How can bays and straits be determined in a procedurally generated map?

What typically incentivizes a professor to change jobs to a lower ranking university?

Collect Fourier series terms

Risk of getting Chronic Wasting Disease (CWD) in the United States?

Has the BBC provided arguments for saying Brexit being cancelled is unlikely?

How to write a macro that is braces sensitive?

To string or not to string

"to be prejudice towards/against someone" vs "to be prejudiced against/towards someone"

How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?

Problem of parity - Can we draw a closed path made up of 20 line segments...

Adding hours to a timestamp column in Sqlite results in NULL, Why?

Sqlite primary key on multiple columnsImprove INSERT-per-second performance of SQLite?Android SQLite timestamp alterationPython datetime to string without microsecond componentHow do DATETIME values work in SQLite?Perform range query on sqlite datetime column or on string column(which having dateTime)SQLite: Column 'date' does not belong to tableWhat's the most efficient way to handle DateTimes, especially since a column of DATETIME results in a NUMERIC?What is a datetime-type column in SQLite?How to allow Superset to recognize that a string is a datetime?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I am getting NULL (nothing) from:

SELECT DATETIME(ServiceCall_CreatedOn,'-6 hours') AS 'Service Created On:' ...

Where ServiceCall_CreatedOn is a Sqlite text column containing:
"2019-01-20 4:48:11" (without the quotes of course)

The following:

SELECT ServiceCall_CreatedOn AS 'Service Created On:' ...

and the following

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' ...

do work!!!!!

Why SQLite DATETIME time subtraction work with a date literal and not with the ServiceCall_CreatedOn column on it? What may be a solution?

sqlite datetime

share|improve this question

edited Mar 8 at 4:17

Nandostyle

asked Mar 8 at 3:57

NandostyleNandostyle

708

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' doesn't work as the format is incorrect i.e. the hour has only 1 digit not the required two. SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' does work.
  
  – MikeT
  Mar 8 at 5:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks MiKET! It worked.
  
  – Nandostyle
  Mar 8 at 13:14
  

  
  
  
  

add a comment | 

0

I am getting NULL (nothing) from:

SELECT DATETIME(ServiceCall_CreatedOn,'-6 hours') AS 'Service Created On:' ...

Where ServiceCall_CreatedOn is a Sqlite text column containing:
"2019-01-20 4:48:11" (without the quotes of course)

The following:

SELECT ServiceCall_CreatedOn AS 'Service Created On:' ...

and the following

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' ...

do work!!!!!

Why SQLite DATETIME time subtraction work with a date literal and not with the ServiceCall_CreatedOn column on it? What may be a solution?

sqlite datetime

share|improve this question

edited Mar 8 at 4:17

Nandostyle

asked Mar 8 at 3:57

NandostyleNandostyle

708

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' doesn't work as the format is incorrect i.e. the hour has only 1 digit not the required two. SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' does work.
  
  – MikeT
  Mar 8 at 5:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks MiKET! It worked.
  
  – Nandostyle
  Mar 8 at 13:14
  

  
  
  
  

add a comment | 

I am getting NULL (nothing) from:

SELECT DATETIME(ServiceCall_CreatedOn,'-6 hours') AS 'Service Created On:' ...

Where ServiceCall_CreatedOn is a Sqlite text column containing:
"2019-01-20 4:48:11" (without the quotes of course)

The following:

SELECT ServiceCall_CreatedOn AS 'Service Created On:' ...

and the following

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' ...

do work!!!!!

Why SQLite DATETIME time subtraction work with a date literal and not with the ServiceCall_CreatedOn column on it? What may be a solution?

sqlite datetime

share|improve this question

edited Mar 8 at 4:17

Nandostyle

asked Mar 8 at 3:57

NandostyleNandostyle

708

share|improve this question

edited Mar 8 at 4:17

Nandostyle

asked Mar 8 at 3:57

NandostyleNandostyle

708

share|improve this question

edited Mar 8 at 4:17

Nandostyle

asked Mar 8 at 3:57

NandostyleNandostyle

708

asked Mar 8 at 3:57

NandostyleNandostyle

708

asked Mar 8 at 3:57

NandostyleNandostyle

708

2 Answers
2

active

oldest

votes

2

I believe that your issue is that you have an invalid format in that you are using 1 digit for the hour not two.

That is

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' 

returns null, whilst

SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' 

returns 2019-01-19 22:48:11

e.g.

SELECT 
 DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On: Version1',
 DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On: Version2'
;

results in

share|improve this answer

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tell you I got blinded after hours coding. This was the problem. I think it was me that manually changed the timestamp for troubleshooting and got into a troubleshooting loop. It is amazing the SQLite gives no error, no warning! Thanks!
  
  – Nandostyle
  Mar 8 at 13:06
  
  

  
  
  
  

add a comment | 

1

SQLite recognizes specific datetime formats as you can see here.

The format in your case is not one of them.

So you need to reformat the values of the column ServiceCall_CreatedOn like this:

SELECT DATETIME(
 case when ServiceCall_CreatedOn like '% _:%' 
 then replace(ServiceCall_CreatedOn, ' ', ' 0') 
 else ServiceCall_CreatedOn 
 end,'-6 hours'
)

It's not elegant but it works by adding a 0 before the digit of hour only when needed.

share|improve this answer

answered Mar 8 at 12:58

forpasforpas

19.8k4830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, the DateTime format got damage when I manually changed the time for troubleshooting purposes. I like your addition to the answer and a possible way to fix a situation where the time format will always be bad. It is not my case, the column is filled by NOW() so I should be OK.
  
  – Nandostyle
  Mar 8 at 13:10
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55056504%2fadding-hours-to-a-timestamp-column-in-sqlite-results-in-null-why%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

I believe that your issue is that you have an invalid format in that you are using 1 digit for the hour not two.

That is

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' 

returns null, whilst

SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' 

returns 2019-01-19 22:48:11

e.g.

SELECT 
 DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On: Version1',
 DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On: Version2'
;

results in

share|improve this answer

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tell you I got blinded after hours coding. This was the problem. I think it was me that manually changed the timestamp for troubleshooting and got into a troubleshooting loop. It is amazing the SQLite gives no error, no warning! Thanks!
  
  – Nandostyle
  Mar 8 at 13:06
  
  

  
  
  
  

add a comment | 

2

I believe that your issue is that you have an invalid format in that you are using 1 digit for the hour not two.

That is

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' 

returns null, whilst

SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' 

returns 2019-01-19 22:48:11

e.g.

SELECT 
 DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On: Version1',
 DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On: Version2'
;

results in

share|improve this answer

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tell you I got blinded after hours coding. This was the problem. I think it was me that manually changed the timestamp for troubleshooting and got into a troubleshooting loop. It is amazing the SQLite gives no error, no warning! Thanks!
  
  – Nandostyle
  Mar 8 at 13:06
  
  

  
  
  
  

add a comment | 

I believe that your issue is that you have an invalid format in that you are using 1 digit for the hour not two.

That is

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' 

returns null, whilst

SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' 

returns 2019-01-19 22:48:11

e.g.

SELECT 
 DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On: Version1',
 DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On: Version2'
;

results in

share|improve this answer

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

SELECT DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On:' 

SELECT DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On:' 

SELECT 
 DATETIME('2019-01-20 4:48:11','-6 hours') AS 'Service Created On: Version1',
 DATETIME('2019-01-20 04:48:11','-6 hours') AS 'Service Created On: Version2'
;

share|improve this answer

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

answered Mar 8 at 5:59

MikeTMikeT

18.6k112844

1

SQLite recognizes specific datetime formats as you can see here.

The format in your case is not one of them.

So you need to reformat the values of the column ServiceCall_CreatedOn like this:

SELECT DATETIME(
 case when ServiceCall_CreatedOn like '% _:%' 
 then replace(ServiceCall_CreatedOn, ' ', ' 0') 
 else ServiceCall_CreatedOn 
 end,'-6 hours'
)

It's not elegant but it works by adding a 0 before the digit of hour only when needed.

share|improve this answer

answered Mar 8 at 12:58

forpasforpas

19.8k4830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, the DateTime format got damage when I manually changed the time for troubleshooting purposes. I like your addition to the answer and a possible way to fix a situation where the time format will always be bad. It is not my case, the column is filled by NOW() so I should be OK.
  
  – Nandostyle
  Mar 8 at 13:10
  

  
  
  
  

add a comment | 

1

SQLite recognizes specific datetime formats as you can see here.

The format in your case is not one of them.

So you need to reformat the values of the column ServiceCall_CreatedOn like this:

SELECT DATETIME(
 case when ServiceCall_CreatedOn like '% _:%' 
 then replace(ServiceCall_CreatedOn, ' ', ' 0') 
 else ServiceCall_CreatedOn 
 end,'-6 hours'
)

It's not elegant but it works by adding a 0 before the digit of hour only when needed.

share|improve this answer

answered Mar 8 at 12:58

forpasforpas

19.8k4830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, the DateTime format got damage when I manually changed the time for troubleshooting purposes. I like your addition to the answer and a possible way to fix a situation where the time format will always be bad. It is not my case, the column is filled by NOW() so I should be OK.
  
  – Nandostyle
  Mar 8 at 13:10
  

  
  
  
  

add a comment | 

SQLite recognizes specific datetime formats as you can see here.

The format in your case is not one of them.

So you need to reformat the values of the column ServiceCall_CreatedOn like this:

SELECT DATETIME(
 case when ServiceCall_CreatedOn like '% _:%' 
 then replace(ServiceCall_CreatedOn, ' ', ' 0') 
 else ServiceCall_CreatedOn 
 end,'-6 hours'
)

It's not elegant but it works by adding a 0 before the digit of hour only when needed.

share|improve this answer

answered Mar 8 at 12:58

forpasforpas

19.8k4830

SELECT DATETIME(
 case when ServiceCall_CreatedOn like '% _:%' 
 then replace(ServiceCall_CreatedOn, ' ', ' 0') 
 else ServiceCall_CreatedOn 
 end,'-6 hours'
)

share|improve this answer

answered Mar 8 at 12:58

forpasforpas

19.8k4830

answered Mar 8 at 12:58

forpasforpas

19.8k4830

answered Mar 8 at 12:58

forpasforpas

19.8k4830